本地动态SQL

最新推荐文章于 2022-09-30 15:34:54 发布

ckrsktoni24621798

最新推荐文章于 2022-09-30 15:34:54 发布

阅读量64

点赞数

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;