这辈子写的第一个PL/SQL过程
2024-07-21 02:40:11
供稿：网友
这辈子写的第一个PL/SQL过程，发现PL/SQL入门也是很简单的。
只有短短的几行，功能更是差多了:查询当前数据库实例名称。
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;
相关说明：
1.必须由dba执行，否则出错。
2.执行前须 set serveroutput on
3.依此方式执行：
exec dbname
附录：同事写的一个PL/SQL，可以试一下。注重：不要用sys用户执行。
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;
执行方法：
set serveroutput on
exec test
我的那份在这儿，可是却不能执行。
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.
今天学了游标，偶终于也写了一个不会出错的了。
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;
嘿嘿，太好了。
