SQL基础（九）PL/SQL

PL/SQL declare、begin、DBMS_SQL、dbms_output[@more@]

■基本篇
set serveroutput on;
declare
cursor c1 is select * from cxck.t订货
where 产品号 = 'a001' order by 订货号;
-- rec c1%rowtype;
begin
dbms_output.put_line ('start...');
for rec in c1 loop
dbms_output.put_line (rec.订货号 || ' ' || rec.产品号);
end loop;
dbms_output.put_line ('end');
exception
when OTHERS then
dbms_output.put_line ('** error **');
end;
/

■动态的 SQL
declare
tbl in varchar2 default 'cxck.test';
cid integer;
begin
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'create table ' || tbl || ' (field1 number(9))', dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
dbms_output.put_line ('end');
exception
when OTHERS then
dbms_output.put_line ('** error **');
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/174388/viewspace-905986/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/174388/viewspace-905986/