做这个实验的目的是为了熟悉函数返回值在sqlplus中的显示,嘿嘿!
我写了个很简单的函数,在sqlpus 中操作的,偷了一下懒我用sys用户测试:
步聚如下:
1、创建一个函数
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> CREATE OR REPLACE FUNCTION f_tpsum(intpn IN VARCHAR2)
2 return VARCHAR2 IS
3 dbtps VARCHAR2(10);
4 BEGIN
5 SELECT sum(bytes/1024/1024)||'M' INTO dbtps FROM dba_data_files
6 WHERE tablespace_name = intpn
7 group by tablespace_name;
8 RETURN dbtps;
9 EXCEPTION
10 WHEN OTHERS THEN
11 RETURN 'no';
12 END;
13 /
Function created.
2、求USERS表空间的大小
SQL> SET serveroutput ON;
SQL> declare
2 tps varchar2(10);
3 begin
4 -- Call the function
5 tps := f_tpsum(intpn => 'USERS'); --传入表空间USERS
6 DBMS_OUTPUT.put_line(tps);
7 end;
8 /
5M --求出是5M
PL/SQL procedure successfully completed.
查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/