17,082
社区成员
发帖
与我相关
我的任务
分享
DECLARE
v_stmt_str VARCHAR2(200);
v_cur_hdl NUMBER;
v_deptnumber NUMBER := 99;
v_deptname VARCHAR2(20);
v_location VARCHAR2(10);
v_rows_processed NUMBER;
BEGIN
v_stmt_str := 'INSERT INTO dept
VALUES (:g_deptno, :g_dname, :g_loc)';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str,
DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_dname', v_deptname);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_loc', v_location);
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
DECLARE
deptname_array DBMS_SQL.VARCHAR2_TABLE;
v_cur_hdl INT;
v_stmt_str VARCHAR2(200);
v_location VARCHAR2(20);
v_deptnumber NUMBER := 10;
v_rows_procsd NUMBER;
BEGIN
v_stmt_str := 'UPDATE dept
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_newloc', v_location);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_ARRAY
(v_cur_hdl, ':g_dname', deptname_array);
-- Execute cursor
v_rows_procsd := DBMS_SQL.EXECUTE(v_cur_hdl);
-- Get RETURNING column into OUT bind array
DBMS_SQL.VARIABLE_VALUE
(v_cur_hdl, ':g_dname', deptname_array);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
[Quote=引用 2 楼 hanvslin 的回复:]