以DDL语句为参数。
create procedure anyddl (s1 varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, s1, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/
SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');
PL/SQL procedure successfully completed.
SQL> desc mytable;
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER
SQL> execute anyddl('drop table mytable');
PL/SQL procedure successfully completed.
PL/SQL中使用动态SQL编程
在PL/SQL程序设计过程中,会遇到很多必须使用动态sql的地方,oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:
function open_cursor:打开一个动态游标,并返回一个整型;
procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;
procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);
procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);
function execute(c in integer):执行游标,并返回处理一个整型,1表示成功,0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;
procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;
以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql
(二)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;
(三)实例应用
1. declare
v_cid integer;
v_updatestr varchar2(100);
v_rowupdated integer;
begin
v_cid:=dbms_sql.open_cursor;
v_updatestr:='update emp set comm=400 where empno=7499';
dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
v_rowupdated:=dbms_sql.execute(v_cid);
dbms_sql.close_cursor(v_cid);
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
2.create or replace function updatecomm(p_comm emp.comm%type, p_empno emp.empno%type
return integer as
v_cid integer;
v_updatestr varchar2(100);
v_rowupdated integer;
begin
v_cid:=dbms_sql.open_cursor;
v_updatestr:='update emp set comm=:comm where empno=:empno';
dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
dbms_sql.bind_variable(v_cid,'comm','p_comm');
dbms_sql.bind_variable(v_cid,'empno','p_empno');
v_rowupdated:=dbms_sql.execute(v_cid);
dbms_sql.close_cursor(v_cid);
return p_rowsupdated;
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
调用--
declare
a integer;
begin
a:=updatecomm(5000,a);
dbms_output.put_line(a);
end;
3.create or replace procedure dynamiccopy(p_deptno1 emp.deptno%type default null,p_deptno2 emp.deptno%type default null)
as
v_cid integer;
v_select varchar2(100);
v_empno char(4);
v_ename varchar2(10);
v_deptno char(2);
v_dummy integer;
begin
v_cid:=dbms_sql.open_cursor;
v_select:='select empno,ename,deptno from emp where deptno in(:d1,:d2)';
dbms_sql.parse(v_cid,v_select,dbms_sql.native);
dbms_sql.bind_variable(v_cid,'d1',p_deptno1);
dbms_sql.bind_variable(v_cid,'d2',p_deptno2);
dbms_sql.define_column(v_cid,1,v_empno,4);
dbms_sql.define_column(v_cid,2,v_ename,10);
dbms_sql.define_column(v_cid,3,v_deptno,2);
v_dummy:=dbms_sql.execute(v_cid);
loop
if dbms_sql.fetch_rows(v_cid)=0 then
exit;
end if;
dbms_sql.column_value(v_cid,1,v_empno);
dbms_sql.column_value(v_cid,2,v_ename);
dbms_sql.column_value(v_cid,3,v_deptno);
insert into emp1(empno,ename,deptno) values(v_empno,v_ename,v_deptno);
end loop;
dbms_sql.close_cursor(v_cid);
commit;
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
4.DDL语句:DDL中联编变量是非法的,即使在解析后不能够调用bind_variable过程。另外,DDL解析后立即执行,不需要调用EXECUTE过程,即使调用了也没有用。
create or replace procedure recreatetable(p_table in varchar2,p_description in varchar2)
as
v_cursor number;
v_createstring varchar2(100);
v_dropstring varchar2(100);
begin
v_cursor:=dbms_sql.open_cursor;
v_dropstring:='drop table'||p_table;
begin
dbms_sql.parse(v_cursor,v_dropstring,dbms_sql.v7);
exception
when others then
if sqlcode!=-942 then
raise;
end if;
end;
v_createstring:='create table'||p_table||p_description;
dbms_sql.parse(v_cursor,v_createstring,dbms_sql.native);
dbms_sql.close_cursor(v_cursor);
exception
when others then
dbms_sql.close_cursor(v_cursor);
raise;
end;
分享到:
相关推荐
oracle中DBMS_SQL的使用,详细讲解oracle DBMS_SQL的使用办法
oracle dbms_sql的使用方法,非常使用
Oracle动态SQL之DBMS_SQL系统包的使用.pdf
ORACLE数据库封装过程DBMS_SQL的应用.pdf
PLSQL开发过程中,动态使用DBMS_SQL[借鉴].pdf
通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考https://blog.csdn.net/weixin_43885834/article/details/105745901 https://download.csdn.net/download/weixin_43885834/12360971...
一个自己开发的dbms系统,实现自己的sql语句编译解析
dbms_sql.parse(l_cursor,'select bus_type as 流程名称,st_center as 结算中心,st_department as 编制部门,bus_desc 流程描述 from xact.tafct23',dbms_sql.native); dbms_sql.describe_columns(l_cursor,l_colcnt,...
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示...5.DISPLAY_SQL_P
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。
java写的数据库 SQL解析器 分客户端和后端底层实现
dbms_random是oracle提供的一个随机函数包,以下介绍一些dbms_random... SQL> select dbms_random.value from dual; VALUE ———- 0.61011338 代码如下: — FUNCTION value RETURN NUMBER; select dbms_random.value f
Java连接Oracle数据库实例 实现SQL*PLUS功能 带JDBC驱动 适合初学者学习参考
Oracle_SQL注入果断分享
SQL Server是由Microsoft开发和推广的关系数据库管理系统(DBMS),它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的,并于1988年推出了第一个OS/2版本。Microsoft SQL Server近年来不断更新版本,1996...
NULL 博文链接:https://wushuangyan26.iteye.com/blog/1307514
一、dbms_job涉及到的知识点 1、创建job: variable jobno number; dbms_job.submit(:jobno, —-job号 'your_procedure;',—-执行的存储过程, ';'不能省略 next_date, —-下次执行时间 'interval' —-每次间隔...
小型的DBMS,支持常用SQL语句,并能实现数据库的基本管路功能
DBMS_SQL.PARSE(P_CursorID,CreateStr,DBMS_SQL.NATIVE); --步骤3:执行语句 P_NUMRows:=DBMS_SQL.EXECUTE(P_CursorID); exception when others then CatchError:=SQLERRM; rollback; commit; --步骤4...
2) 使用包DBMS_DEBUG和 DBMS_TRACE跟踪调试PL/SQL应用。 3) 使用包DBMS_JOB调度PL/SQL任务在指定时间自动运行。 4) 使用包UTE_FILE直接读写操作系统文件。 5) 使用Pro*C和嵌入SQL语言编制外部例程。 6) 使用动态...