• 动态SQL


     

    范例:利用动态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 ;

    /

    范例:直接在程序中编写DDLDML

    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 ;

    /

  • 相关阅读:
    ASP.WEB Form 几点知识
    feign 发送请求时,传多个参数时的写法
    springboot 关于log4j日志配置
    springboot+swagger
    整合Spring Data JPA与Spring MVC: 分页和排序pageable
    jpa 自定义sql 删除方法注意点
    jpa 中的save()方法
    mybatis javabean字段与数据库字段的映射
    分布式配置中心(Spring Cloud Config) (问题解答)
    微服务中的rpc 请求写法
  • 原文地址:https://www.cnblogs.com/doudouxiaoye/p/5804379.html
Copyright © 2020-2023  润新知