做这个实验的目的是为了熟悉函数返回值在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
我写了个很简单的函数,在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.
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036