--==================================================== --======================================= 二.pl/sql 编程方面 --====================================================== 1.自治事务:8i以上版本,不影响主事务。 在存储过程的is\as 后面声明PRAGMA AUTONOMOUS_TRANSACTION; 自治事务防止嵌套提交,使事务在自己的事务区内提交或回滚不会影响其他的事务。 2、包 包说明(package specification),包头,存放关于包的内容的信息,定义包的用户可见的过程、 函数,数据类型和变量 create or replace package tt_aa as v1 varchar2(10); v2 varchar2(10); v3 number; v4 boolean; procedure proc1(x number); procedure proc2(y varchar2); procedure proc3(z number); function my_add(x number,y number) return number; end; 包主体(package body)是可选的 create or replace package body tt_aa as procedure proc1(x number) as begin v1:=to_char(x); end; procedure proc2(y varchar2) as begin v2:=y; end; procedure proc3(z number) as begin v1:=z; end; procedure proc4(x number,y number) return number as begin return x+y; end; end; 调用 begin tt_aa.proc1(6); dbms_output.put_line(to_char(tt_aa.my_add(1,3)); end; 3、动态sql(使用dbms_sql) create or replace procedure my_execute(sql_string in varchar2) as v_cursor number; v_numrows interger; begin v_cursor:=dbms_sql.open_cursor; dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7); v_numrows:=dbms_sql.execute(v_cursor); dbms_sql.close_cursor(v_cursor); end; 则可以 sql>exec my_execute(''''select * from tab''''); sql>exec my_execute(''''insert into test value''''||''''(''''||''''''''''''ddd''''''''''''||'''')''''); sql>exec my_execute(''''commit''''); 对于查询方面的可以如下方式: 比如想用游标查询一个表,但是这个表是分月的,每个月可能表名都会改变。 create or replace procedure proc_test as v_curid integer; v_result integer; v_strSql varchar2(255); v_userid okcai.userid%type; v_username okcai.username%type; begin v_strSql := ''''select * from okcai_''''||to_char(sysdate,''''yyyymm''''); v_curid := dbms_sql.open_cursor; dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7); dbms_sql.define_column(v_curid,1,v_userid); dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小 v_result := dbms_sql.execute(v_curid); loop if dbms_sql.fetch_rows(v_curid) = 0 then exit; --没有了 ,退出循环 end if; dbms_sql.column_value(v_curid,1,v_userid); dbms_sql.column_value(v_curid,2,v_username); dbms_output.put_line(v_userid); dbms_output.put_line(v_username); end loop; dbms_sql.close(v_curid); end; 4、用EXECUTE IMMEDIATE <1>. 在PL/SQL运行DDL语句 begin execute immediate ''''set role all''''; end; <2>. 给动态语句传值(USING 子句) declare l_depnam varchar2(20) := ''''testing''''; l_loc varchar2(10) := ''''Dubai''''; begin execute immediate ''''insert into dept values (:1, :2, :3)'''' using 50, l_depnam, l_loc; commit; end; <3>. 从动态语句检索值(INTO子句) declare l_cnt varchar2(20); begin execute immediate ''''select count(1) from emp'''' into l_cnt; dbms_output.put_line(l_cnt); end; <4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定 declare l_routin varchar2(100) := ''''gen2161.get_rowcnt''''; l_tblnam varchar2(20) := ''''emp''''; l_cnt number; l_status varchar2(200); begin execute immediate ''''begin '''' || l_routin || ''''(:2, :3, :4); end;'''' using in l_tblnam, out l_cnt, in out l_status; if l_status != ''''OK'''' then dbms_output.put_line(''''error''''); end if; end; <5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量 declare type empdtlrec is record (empno number(4), ename varchar2(20), deptno number(2)); empdtl empdtlrec; begin execute immediate ''''select empno, ename, deptno '''' || ''''from emp where empno = 7934'''' into empdtl; end; <6>. 传递并检索值.INTO子句用在USING子句前 declare l_dept pls_integer := 20; l_nam varchar2(20); l_loc varchar2(20); begin execute immediate ''''select dname, loc from dept where deptno = :1'''' into l_nam, l_loc using l_dept ; end; <7>. 多行查询选项.对此选项用insert语句填充临时表,用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾. declare l_sal pls_integer := 2000; begin execute immediate ''''insert into temp(empno, ename) '''' || '''' select empno, ename from emp '''' || '''' where sal > :1'''' using l_sal; commit; end; <8>. 完成update的returning功能 update可以用returning返回修改以后的值。比如: UPDATE employees SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140 WHERE last_name = ’Jones’ RETURNING salary*0.25, last_name, department_id INTO :bnd [1] [2] 下一页 [Access]sql随机抽取记录 [Access]ASP&SQL让select查询结果随机排序的实现方法 [系统软件]SQL语句性能优化--LECCO SQL Expert [C语言系列]SQL Server到DB2连接服务器的实现 [C语言系列]SQL Server到SYBASE连接服务器的实现 [C语言系列]SQL Server到SQLBASE连接服务器的实现 [C语言系列]SQL Server连接VFP数据库的实现 [C语言系列]ASP+SQL Server之图象数据处理 [C语言系列]SQL Server连接ACCESS数据库的实现 [C语言系列]DBA的最佳选择—图形界面还是T-SQL命令?
|