create or replace PRocedure dbname is v_dbname varchar2(30);
begin select name into v_dbname from v$database; dbms_output.put_line('db name is: 'v_dbname); end;
相關(guān)說明： 1.必須由dba執(zhí)行，否則出錯。 2.執(zhí)行前須 set serveroutput on 3.依此方式執(zhí)行： exec dbname
附錄：同事寫的一個PL/SQL，可以試一下。注重：不要用sys用戶執(zhí)行。
create or replace procedure test is v_tname tab.tname%type; v_count number; cursor tab_cursor is select tname from tab; begin open tab_cursor; loop fetch tab_cursor into v_tname; execute immediate 'select count(*) from 'v_tname into v_count ; dbms_output.put_line(v_tname' 'to_char(v_count)); exit when tab_cursor%notfound; end loop; close tab_cursor; end;
執(zhí)行方法： set serveroutput on exec test
我的那份在這兒，可是卻不能執(zhí)行。 create or replace procedure tabcount is v_tname varchar2(30); v_count number; v_size number;
begin select count(*) into v_size from tab; loop select tname into v_tname from tab where rownum=v_size; execute immediate 'select count(*) from 'v_tname into v_count; dbms_output.put_line(v_tname' 'to_char(v_count)); v_size:=v_size-1; exit when v_size=0; end loop; end;
ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.TABCOUNT", line 8 ORA-06512: at line 1
========= added by 20050804. 今天學(xué)了游標(biāo)，偶終于也寫了一個不會出錯的了。
create or replace procedure tabcount is v_tname varchar2(30); cursor sor is select tname from tab; v_count number; begin open sor; dbms_output.enable(200000); v_count:=0; loop fetch sor into v_tname; dbms_output.put_line('tname is 'v_tname); v_count:=v_count+1; exit when sor%NOTFOUND; end loop; dbms_output.put_line('counts of tname is 'to_char(v_count)); close sor; end;
