范例:利用动态SQL在执行时创建一张数据表
CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_statement VARCHAR2(200) ; -- 定义操作的SQL语句 v_count NUMBER ; -- 保存表中记录 BEGIN SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ; IF v_count = 0 THEN -- 数据表不存在 v_sql_statement := 'CREATE TABLE ' || p_table_name || ' ( id NUMBER , name VARCHAR2(30) NOT NULL , CONSTRAINT pk_id_' || p_table_name || ' PRIMARY KEY(id)) ' ; -- 创建数据表 EXECUTE IMMEDIATE v_sql_statement ; -- 执行动态SQL END IF ; v_sql_statement := 'SELECT COUNT(*) FROM ' || p_table_name ; -- 查询数据表记录 EXECUTE IMMEDIATE v_sql_statement INTO v_count ; -- 执行动态SQL并保存数据记录 RETURN v_count ; END ; / |
范例:直接在程序中编写DDL或DML
CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_statement VARCHAR2(200) ; -- 定义操作的SQL语句 v_count NUMBER ; -- 保存表中记录 BEGIN SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ; IF v_count = 0 THEN -- 数据表不存在 -- 错误:无法直接使用DDL操作 CREATE TABLE p_table_name ( idNUMBER, nameVARCHAR2(30)NOT NULL , CONSTRAINT id_pk PRIMARY KEY(id)) ; END IF ; -- 错误:查询数据表不存在 SELECT COUNT(*) INTO v_count FROM p_table_name ; RETURN v_count ; END ; / |
范例:编写PL/SQL块调用函数
BEGIN DBMS_OUTPUT.put_line('数据表记录:' || get_table_count_fun('mldnjava')) ; END ; / |
范例:为c##scott用户授权
CONN sys/change_on_install AS SYSDBA ; GRANT CREATE ANY TABLE TO c##scott ; CONN c##scott/tiger ; |
范例:使用动态SQL创建表和PL/SQL块
DECLARE v_sql_statement VARCHAR2(200) ; v_count NUMBER ; -- 保存查找结果 BEGIN SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name='MLDN_TAB' ; IF v_count = 0 THEN -- 数据表不存在 v_sql_statement := 'CREATE TABLE mldn_tab( id NUMBER PRIMARY KEY , url VARCHAR2(50) NOT NULL)' ; -- 定义动态SQL EXECUTE IMMEDIATE v_sql_statement ; ELSE -- 数据表存在 v_sql_statement := 'TRUNCATE TABLE mldn_tab' ; EXECUTE IMMEDIATE v_sql_statement ; END IF ; v_sql_statement := 'BEGIN FOR x IN 1 .. 10 LOOP INSERT INTO mldn_tab(id,url) VALUES (x , ''www.mldnjava.cn - '' || x) ; END LOOP ; END ;' ; EXECUTE IMMEDIATE v_sql_statement ; COMMIT ; -- 提交事务 END ; / |
范例:查询mldn_tab数据表
SELECT * FROM mldn_tab ; |
范例:使用绑定变量
DECLARE v_sql_statement VARCHAR2(200) ; v_deptno dept.deptno%TYPE := 60 ; v_dname dept.dname%TYPE := 'MLDN' ; v_loc dept.loc%TYPE := '北京' ; BEGIN v_sql_statement := 'INSERT INTO dept(deptno,dname,loc) VALUES (:dno , :dna , :dl)' ; EXECUTE IMMEDIATE v_sql_statement USING v_deptno,v_dname,v_loc ; COMMIT ; END ; / |
范例:利用集合更新多条记录
DECLARE v_sql_statement VARCHAR2(200) ; TYPE deptno_nested IS TABLE OF dept.deptno%TYPE NOT NULL ; TYPE dname_nested IS TABLE OF dept.dname%TYPE NOT NULL ; v_deptno deptno_nested := deptno_nested(10,20,30,40) ; v_dname dname_nested := dname_nested('财务部','研发部','销售部','操作部') ; BEGIN v_sql_statement := 'UPDATE dept SET dname=:dna WHERE deptno=:dno' ; FOR x IN 1 .. v_deptno.COUNT LOOP EXECUTE IMMEDIATE v_sql_statement USING v_dname(x),v_deptno(x) ; END LOOP ; COMMIT ; END ; / |
范例:更新完成后查询dept表记录
SELECT * FROM dept ; |
范例:查询数据
DECLARE v_sql_statement VARCHAR2(200) ; v_empno emp.empno%TYPE := 7369 ; v_emprow emp%ROWTYPE ; BEGIN v_sql_statement := 'SELECT * FROM emp WHERE empno=:eno' ; EXECUTE IMMEDIATE v_sql_statement INTO v_emprow USING v_empno ; DBMS_OUTPUT.put_line('雇员编号:' || v_emprow.empno || ',姓名:' || v_emprow.ename || ',职位:' || v_emprow.job) ; END ; / |
范例:在创建表时使用绑定变量
DECLARE v_sql_statement VARCHAR2(200) ; v_table_name VARCHAR2(200) := 'mldn' ; v_id_column VARCHAR2(200) := 'id' ; BEGIN v_sql_statement := 'CREATE TABLE :tn (:ci NUMBER PRIMARY KEY)' ; EXECUTE IMMEDIATE v_sql_statement USING v_table_name,v_id_column ; END ; / |
范例:正确的代码
DECLARE v_sql_statement VARCHAR2(200) ; v_table_name VARCHAR2(200) := 'mldn' ; v_id_column VARCHAR2(200) := 'id' ; BEGIN v_sql_statement := 'CREATE TABLE ' || v_table_name ||' (' || v_id_column ||' NUMBER PRIMARY KEY)' ; EXECUTE IMMEDIATE v_sql_statement ; END ; / |
范例:更新数据,取得更新后的结果
DECLARE v_sql_statement VARCHAR2(200) ; -- 定义SQL操作语句 v_empno emp.empno%TYPE := 7369 ; -- 要更新的雇员编号 v_salary emp.sal%TYPE ; -- 保存更新后的sal内容 v_job emp.job%TYPE ; -- 保存更新后的job内容 BEGIN v_sql_statement := 'UPDATE emp SET sal=sal*1.2,job=''开发'' ' || ' WHERE empno=:eno RETURNING sal,job INTO :salary,:job' ; EXECUTE IMMEDIATE v_sql_statement USING v_empno RETURNING INTO v_salary,v_job ; DBMS_OUTPUT.put_line('调整后的工资:' || v_salary || ',新的职位:' || v_job) ; END ; / |
范例:删除数据,取得删除前的结果
DECLARE v_sql_statement VARCHAR2(200) ; -- 定义SQL操作语句 v_emprow emp%ROWTYPE ; -- 保存emp类型 v_empno emp.empno%TYPE := 7369 ; -- 删除的雇员编号 v_ename emp.ename%TYPE ; -- 删除的雇员姓名 v_sal emp.sal%TYPE ; -- 删除的雇员工资 BEGIN v_sql_statement := 'DELETE FROM emp WHERE empno=:eno RETURNING ename,sal INTO :name,:sal' ; EXECUTE IMMEDIATE v_sql_statement USING v_empno RETURNING INTO v_ename,v_sal ; DBMS_OUTPUT.put_line('删除的雇员编号:' || v_empno || ',姓名:' || v_ename || ',工资:' || v_sal) ; END ; / |
范例:编写部门增加过程
CREATE OR REPLACE PROCEDURE dept_insert_proc( p_deptno IN OUT dept.deptno%TYPE , -- 此处可以将p_deptno的内容回传 p_dname dept.dname%TYPE, -- 默认为IN模式 p_loc dept.loc%TYPE) AS -- 默认为IN模式 BEGIN SELECT MAX(deptno) INTO p_deptno FROM dept ; -- 取得最大的deptno内容 p_deptno := p_deptno + 1 ; -- 让最大值部门编号加1,此处不考虑超过2位数字情况 INSERT INTO dept(deptno,dname,loc) VALUES (p_deptno,p_dname,p_loc) ; END ; / |
范例:编写PL/SQL块,调用过程
DECLARE v_sql_statement VARCHAR2(200) ; v_deptno dept.deptno%TYPE ; v_dname dept.dname%TYPE := 'MLDN教学部' ; v_loc dept.loc%TYPE := '北京' ; BEGIN v_sql_statement := 'BEGIN dept_insert_proc(:dno , :dna , :dl) ; END ;' ; -- 定义PL/SQL块 EXECUTE IMMEDIATE v_sql_statement USING IN OUT v_deptno , IN v_dname , v_loc ; DBMS_OUTPUT.put_line('新增部门编号为:' || v_deptno) ; END ; / |
范例:更新时使用BULK COLLECT语句
DECLARE TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; v_ename ename_index ; v_job job_index ; v_sal sal_index ; v_sql_statement VARCHAR2(200) ; -- 定义动态SQL v_deptno emp.deptno%TYPE := 10 ; -- 查询10部门 BEGIN v_sql_statement := 'UPDATE emp SET sal=sal*1.2 WHERE deptno=:dno ' || ' RETURNING ename,job,sal INTO :ena, :ej, :es' ; -- 此时返回多行更新结果 EXECUTE IMMEDIATE v_sql_statement USING v_deptno RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ; FOR x IN 1 .. v_ename.COUNT LOOP DBMS_OUTPUT.put_line('雇员姓名:' || v_ename(x) || ',职位:' || v_job(x) || ',工资:' || v_sal(x)) ; END LOOP ; END ; / |
范例:查询时使用BULK COLLECT
DECLARE TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员姓名 TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员职位 TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; -- 保存雇员工资 v_ename ename_index ; v_job job_index ; v_sal sal_index ; v_sql_statement VARCHAR2(200) ; -- 定义动态SQL v_deptno emp.deptno%TYPE := 10 ; -- 查询10部门 BEGIN v_sql_statement := 'SELECT ename,job,sal FROM emp WHERE deptno=:dno' ; -- 此时返回多行更新结果 EXECUTE IMMEDIATE v_sql_statement BULK COLLECT INTO v_ename,v_job,v_sal USING v_deptno ; -- 将多个结果一起返回 FOR x IN 1 .. v_ename.COUNT LOOP DBMS_OUTPUT.put_line('雇员姓名:' || v_ename(x) || ',职位:' || v_job(x) || ',工资:' || v_sal(x)) ; END LOOP ; END ; / |
范例:通过FORALL设置多个参数
DECLARE TYPE empno_nested IS TABLE OF emp.empno%TYPE ; -- 定义嵌套表 TYPE ename_index IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表 TYPE job_index IS TABLE OF emp.job%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表 TYPE sal_index IS TABLE OF emp.sal%TYPE INDEX BY PLS_INTEGER ; -- 定义索引表 v_ename ename_index ; -- 保存删除后的姓名 v_job job_index ; -- 保存删除后的职位 v_sal sal_index ; -- 保存删除后的工资 v_empno empno_nested := empno_nested(7369,7566,7788) ; -- 定义要删除雇员编号 v_sql_statement VARCHAR2(200) ; -- 动态SQL BEGIN v_sql_statement := 'DELETE FROM emp WHERE empno=:eno ' || ' RETURNING ename,job,sal INTO :ena , :ej , :es' ; -- 删除数据SQL FORALL x IN 1 .. v_empno.COUNT -- FORALL绑定多个变量 EXECUTE IMMEDIATE v_sql_statement USING v_empno(x) RETURNING BULK COLLECT INTO v_ename,v_job,v_sal ; FOR x IN 1 .. v_ename.COUNT LOOP DBMS_OUTPUT.put_line('雇员姓名:' || v_ename(x) || ',职位:' || v_job(x) || ',工资:' || v_sal(x)) ; END LOOP ; END ; / |
范例:在游标中使用动态SQL
DECLARE cur_emp SYS_REFCURSOR ; -- 定义游标变量 v_emprow emp%ROWTYPE ; -- 定义emp行类型 v_deptno emp.deptno%TYPE := 10 ; -- 定义要查询雇员的部门编号 BEGIN OPEN cur_emp FOR 'SELECT * FROM emp WHERE deptno=:dno ' USING v_deptno ; LOOP FETCH cur_emp INTO v_emprow ; -- 取得游标数据 EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有数据则退出 DBMS_OUTPUT.put_line('雇员姓名:' || v_emprow.ename || ',雇员职位:' || v_emprow.job) ; END LOOP ; CLOSE cur_emp ; END ; / |
范例:利用FETCH保存查询结果
DECLARE cur_emp SYS_REFCURSOR ; -- 定义游标变量 TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER ; -- 定义索引表 v_emprow emp_index ; -- 定义emp行类型 v_deptno emp.deptno%TYPE := 10 ; -- 定义要查询雇员的部门编号 BEGIN OPEN cur_emp FOR 'SELECT * FROM emp WHERE deptno=:dno' USING v_deptno ; FETCH cur_emp BULK COLLECT INTO v_emprow ; CLOSE cur_emp ; FOR x IN 1 .. v_emprow.COUNT LOOP DBMS_OUTPUT.put_line('雇员编号:' || v_emprow(x).empno || ',姓名:' || v_emprow(x).ename || ',职位:' || v_emprow(x).job) ; END LOOP ; END ; / |
范例:查看DBMS_SQL包定义
SELECT * FROM all_source WHERE type='PACKAGE' AND name='DBMS_SQL' ; |
范例:通过DBMS_SQL包查询数据
DECLARE v_sql_statement VARCHAR2(200) ; v_cid NUMBER ; -- 保存游标ID,以方便关闭 v_ename emp.ename%TYPE ; v_job emp.job%TYPE ; v_sal emp.sal%TYPE ; v_stat NUMBER ; v_deptno emp.deptno%TYPE := 10 ; -- 部门编号 BEGIN v_cid := DBMS_SQL.open_cursor ; -- 打开游标 v_sql_statement := 'SELECT ename,job,sal FROM emp WHERE deptno=:dno' ; DBMS_SQL.parse(v_cid , v_sql_statement ,DBMS_SQL.native) ; DBMS_SQL.define_column(v_cid , 1 , v_ename, 10) ; -- 定义OUT模式变量 DBMS_SQL.define_column(v_cid , 2 , v_job, 9) ; -- 定义OUT模式变量 DBMS_SQL.define_column(v_cid , 3 , v_sal) ; -- 定义OUT模式变量 DBMS_SQL.bind_variable(v_cid, ':dno', v_deptno); --绑定变量 v_stat := DBMS_SQL.execute(v_cid) ; -- 执行游标,返回更新行数 LOOP EXIT WHEN DBMS_SQL.fetch_rows(v_cid)=0 ; DBMS_SQL.column_value(v_cid , 1 , v_ename) ; DBMS_SQL.column_value(v_cid , 2 , v_job) ; DBMS_SQL.column_value(v_cid , 3 , v_sal) ; DBMS_OUTPUT.put_line('雇员姓名:' || v_ename || ',职位:' || v_job || ',薪金:' || v_sal) ; END LOOP ; DBMS_SQL.close_cursor(v_cid) ; -- 关闭游标 END ; / |
DECLARE v_sql_statement VARCHAR2(200) ; v_cid NUMBER ; -- 保存游标ID,以方便关闭 v_comm emp.comm%TYPE :=500 ; v_empno emp.empno%TYPE := 7369 ; v_stat NUMBER ; BEGIN v_cid := DBMS_SQL.open_cursor ; -- 打开游标 v_sql_statement := 'UPDATE emp SET comm=:ec WHERE empno=:eno' ; DBMS_SQL.parse(v_cid , v_sql_statement ,DBMS_SQL.native) ; DBMS_SQL.bind_variable(v_cid, ':ec', v_comm); --绑定变量 DBMS_SQL.bind_variable(v_cid, ':eno', v_empno); --绑定变量 v_stat := DBMS_SQL.execute(v_cid) ; -- 执行游标,返回更新行数 DBMS_OUTPUT.put_line('更新行数为:' || v_stat) ; DBMS_SQL.close_cursor(v_cid) ; -- 关闭游标 END ; / |