oracle 动态sql 绑定变量

--无绑定变量的非SQL查询
DECLARE

v_sql varchar2(4000);
BEGIN --使用EXECUTE IMMEDIATE执行动态SQL,最后提交事物
v_sql := 'CREATE TABLE account('||' id NUMBER(3) NOT NULL,'||
'name VARCHAR2(50) NOT NULL,'||' balance NUMBER(8,2) NOT NULL,'||
' btime DATE NOT NULL)';
EXECUTE IMMEDIATE v_sql;
v_sql :='INSERT INTO account (id,name,balance,btime)'||
' VALUES (1,''张三'',2000.1,TO_DATE(''12-02-2008'',''dd-mm-yyyy''))';
EXECUTE IMMEDIATE v_sql;
v_sql :='INSERT INTO account (id,name,balance,btime)'||
' VALUES (2,''李四'',530,TO_DATE(''10-12-2008'',''dd-mm-yyyy''))';
EXECUTE IMMEDIATE v_sql;
v_sql :='INSERT INTO account (id,name,balance,btime)'||
' VALUES (3,''王五'',1631,TO_DATE(''11-05-2007'',''dd-mm-yyyy''))';
EXECUTE IMMEDIATE v_sql;
v_sql :='INSERT INTO account (id,name,balance,btime)'||
' VALUES (4,''小强'',910.3,TO_DATE(''12-04-2008'',''dd-mm-yyyy''))';
EXECUTE IMMEDIATE v_sql;
v_sql :='INSERT INTO account (id,name,balance,btime)'||
' VALUES (5,''小周'',8700,TO_DATE(''11-11-2008'',''dd-mm-yyyy''))';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

drop table account


--带固定数目绑定变量的非SQL查询
DECLARE

v_sql varchar2(4000);

v_balance account.balance%TYPE;

v_name account.name%TYPE;
BEGIN --使用EXCUTE IMMEDIATE语句执行SQL字符串，使用USING将绑定变量与输入的ID和账户金额进行绑定，
--将更新后的账户名称与账户余额返回到变量中
v_sql := 'UPDATE account SET balance=:balance WHERE id=:id'||
' RETURNING name,balance INTO:1,:2';
EXECUTE IMMEDIATE v_sql
USING &balance,&id --把绑定参数的值传给绑定变量在sql语句中分别有指定
RETURNING INTO v_name, v_balance; --返回的数据传给两个定义变量
IF SQL%NOTFOUND THEN --假如没找到就抛出一个异常
RAISE_APPLICATION_ERROR(-20001,'无效的账户ID');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_balance);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


--带固定数目列和绑定变量的SQL查询
DECLARE

TYPE account_table_type IS TABLE OF account%ROWTYPE INDEX BY BINARY_INTEGER;

account_table account_table_type;

account_cursor SYS_REFCURSOR;

v_sql varchar2(4000);
BEGIN
v_sql := 'SELECT * FROM account WHERE TO_CHAR(btime,''YYYY'') = :year'; --通过年份使用绑定变量
OPEN account_cursor FOR v_sql USING &year; --open。。for执行sql字符串，使用using进行变量绑定
FETCH account_cursor BULK COLLECT INTO account_table; --执行下一条
IF account_table.COUNT = 0 THEN --集合长度等于0就抛出异常
RAISE_APPLICATION_ERROR(-20001,'未找到数据');
END IF;
FOR i IN 1..account_table.COUNT LOOP --把集合中内容打印出来
DBMS_OUTPUT.PUT_LINE(account_table(i).name || ' ' || account_table(i).balance);
END LOOP;
CLOSE account_cursor; --关闭游标
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;