ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 本地动态SQL

本地动态SQL

原创 Linux操作系统 作者:hjianping 时间:2011-04-26 19:59:39 0 删除 编辑

1、数据查询语言 (select)
2、数据操纵语言 DML (insert,update,delete)
3、数据定义语言 DDL (create,alter,drop)
4、数据控制语言 DCL (grant,revoke)
5、事务控制语言 TCS (commit,rollback,savepoint)
6、会话控制语言 SCS (alter session,set role)
7、系统控制语言 SCS (alter system)

declare
 v_name varchar2(2):='&input1';
 v_value varchar2(2):='&input2';
 c_name varchar2(2):=substr(v_name,1,1);
 l_no number(1):=to_number(substr(v_name,2,1));
 p varchar2(2);
 sql_text varchar2(100):='select '||c_name||' from tb_test where id='||l_no;
begin
 execute immediate sql_text into p;
 dbms_output.put_line(p);
 if p=v_value then
  dbms_output.put_line('YES');
 else
  dbms_output.put_line('NO');
 end if;
end;
/


1、处理数据定义语言DDL
declare
 ddl_string varchar2(1000):='create table temp_dept
 (deptno number(2),
  dname varchar2(10),
  loc varchar2(10),
  constraint temp_pk_dept primary key (deptno)
 )';
begin
 execute immediate ddl_string;
end;
/

2、处理数据操作语言DCL
declare
 dcl_string varchar2(1000):='&string';
begin
 execute immediate ddl_string;
end;
/
输入 string 的值: grant select on temp_dept to hjp

3、处理数据操纵语言DMS
A、无站位符
declare
 dml_stat varchar2(100):='&string';
begin
 execute immediate dml_stat;
end;
/
输入 string 的值: update emp set sal=1800 where empno=7788

B、有站位符
declare
 dml_stat varchar2(100):='insert into emp(empno,ename,sal) values (:no,:name,:sal)';
begin
 execute immediate dml_stat using &no,&name,&sal;
end;
/
输入 no 的值:  8000
输入 name 的值: 'hjp'
输入 sal 的值:  5000

C、包含returning子句
declare
 dml_stat varchar2(100):='delete emp where empno=&eno returning ename into :name';
 v_name emp.ename%type;
begin
 execute immediate dml_stat returning into v_name;
 dbms_output.put_line(v_name||'被开除');
end;
/

declare
 v_name emp.ename%type;
begin
 delete emp where empno=&eno returning ename into v_name;
 dbms_output.put_line(v_name||'被开除');
end;
/

D、有站位符和returning子句
declare
 dml_stat varchar2(100):='update emp set sal=:salary
 where empno=:eno return sal into :=newsal';                         
 v_sal emp.sal%type;
begin
 execute immediate dml_stat using &sal,&eno returning into v_sal;
 dbms_output.put_line('新工资:'||v_sal);
end;
/


4、处理多行查询

A、在动态DML上使用批量绑定
declare
       query_stat varchar2(100):='select * from emp where lower(ename)=lower(:name)';
       emp_record emp%rowtype;
begin
     execute immediate query_stat into emp_record using &name;
     dbms_output.put_line(emp_record.empno||' '||emp_record.sal);
end;
/

B、在DML返回子句上使用批量绑定
declare
       type ename_table_type is table of emp.ename%type;
       type sal_table_type is table of emp.sal%type;
       ename_table ename_table_type;
       sal_table sal_table_type;
       sql_stat varchar2(100):='update emp set sal=sal+200
       where deptno=:dno returning ename,sal into :n,:s';
begin
     execute immediate sql_stat using &dno returning bulk collect into ename_table,sal_table ;
     for i in 1..ename_table.count loop
         dbms_output.put_line(ename_table(i)||' '||sal_table(i));
     end loop;
end;

C、使用批量绑定处理多行查询
declare
       type ename_table_type is table of emp.ename%type;
       type sal_table_type is table of emp.sal%type;
       ename_table ename_table_type;
       sal_table sal_table_type;
       sql_stat varchar2(100):='select ename,sal from emp where deptno=:dno';
begin
     execute immediate sql_stat bulk collect into ename_table,sal_table using &dno;
     for i in 1..ename_table.count loop
         dbms_output.put_line(ename_table(i)||' '||sal_table(i));
     end loop;
end;
/

D、在FETCH语句中使用批量提取
declare
       type emp_cursor_type is ref cursor;
       emp_cursor emp_cursor_type;
       type ename_table_type is table of emp.ename%type;
       type sal_table_type is table of emp.sal%type;
       type job_table_type is table of emp.job%type;
       ename_table ename_table_type;
       sal_table sal_table_type;
       job_table job_table_type;
       sql_stat varchar2(100):='select ename,sal,job from emp where deptno=:dno';
       aa int:=&a;
begin
     if aa=1 then
        open emp_cursor for sql_stat using &dno;
        fetch emp_cursor bulk collect  into ename_table,sal_table,job_table;
        close emp_cursor;
        for i in 1..ename_table.count loop
            dbms_output.put_line(ename_table(i)||' '||sal_table(i)||' '||job_table(i));
        end loop;
     else
         open emp_cursor for sql_stat using &dno;
         fetch emp_cursor bulk collect  into ename_table,sal_table,job_table;
         close emp_cursor;
         for i in 1..ename_table.count loop
             dbms_output.put_line(ename_table(i)||' '||sal_table(i));
         end loop;
     end if; 
end;

 

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

上一篇: 加密存储过程
下一篇: CLOB and BFILE
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72823