本地动态sql例子 DECLARE sql_stmt VARCHAR2(200); plsql_block VARCHAR2(500); emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := ’PERSONNEL’; location VARCHAR2(13) := ’DALLAS’; emp_rec emp%ROWTYPE;BEGIN EXECUTE IMMEDIATE ’CREATE TABLE bonus (id NUMBER, amt NUMBER)’; sql_stmt := ’INSERT INTO dept VALUES (:1, :2, :3)’;EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := ’SELECT * FROM emp WHERE empno = :id’;EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; plsql_block := ’BEGIN emp_pkg.raise_salary(:id, :amt); END;’;EXECUTE IMMEDIATE plsql_block USING 7788, 500; sql_stmt := ’UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2’;EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE ’DELETE FROM dept WHERE deptno = :num’ USING dept_id;EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’; END;
create or replace procedure p_test(v_count out integer)isbegin select count(1) into v_count from tablename; end p_test;
CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); END pkg_test; /CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get (p_id NUMBER, p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN OPEN p_rc FOR SELECT ID, NAME, sex, address, postcode, birthday FROM student; ELSE sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; /
as
..
..
begin
insert into tbname values(....);
drop from tbname where .......;
update tbname set .......;
end;
静态sql语句就是常用的sql,如:
select * from tab;如果sql语句的某部分需要动态变化,则静态sql语句就不太方便了,这时把sql语句写到字符串变量里,再执行字符串里的sql语句,如:
ls_sql := 'select ' || ls_col || ' from ' || ls_tname;
execute immediate ls_sql;
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ’PERSONNEL’;
location VARCHAR2(13) := ’DALLAS’;
emp_rec emp%ROWTYPE;BEGIN
EXECUTE IMMEDIATE ’CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ’INSERT INTO dept VALUES (:1, :2, :3)’;EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := ’SELECT * FROM emp WHERE empno = :id’;EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := ’BEGIN emp_pkg.raise_salary(:id, :amt); END;’;EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := ’UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2’;EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE ’DELETE FROM dept WHERE deptno = :num’
USING dept_id;EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;
select count(1)
into v_count
from tablename;
end p_test;
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/