Oracle PL/SQL Tutorial/System Packages/DBMS SQL
Содержание
- 1 An example of using DBMS_SQL.OPEN_CURSOR
- 2 Assign date with DBMS_SQL package
- 3 Close a cursor
- 4 Create Pl/SQL block dynamically and then execute it by calling "DBMS_SQL.EXECUTE"
- 5 DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
- 6 DBMS_SQL.EXECUTE an update statement
- 7 DBMS_SQL.LAST_ERROR_POSITION
- 8 dbms_sql.number_table
- 9 DBMS_SQL.PARSE
- 10 DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
- 11 Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
- 12 Dump query with dbms_sql
- 13 Package for running dynamic sql based on DBMS_SQL package
- 14 Print table with dynamic query
- 15 Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
- 16 Use dbms_sql.describe_columns
- 17 Use DBMS_SQL package to parse math expression
- 18 Use DBMS_SQL to update a table and get the number of rows updated
- 19 Use dynamic SQL to check the business logic
- 20 Wrap dbms_sql package
An example of using DBMS_SQL.OPEN_CURSOR
SQL>
SQL> DECLARE
2 tableName VARCHAR2(30):="temp";
3 sqlString VARCHAR2(150);
4 c INTEGER;
5 ret_code INTEGER;
6 BEGIN
7 sqlString :="create table "||tableName||"(col1 NUMBER NOT NULL)";
8 c :=DBMS_SQL.OPEN_CURSOR;
9 DBMS_SQL.PARSE(c,sqlString,DBMS_SQL.V7);
10 ret_code :=DBMS_SQL.EXECUTE(c);
11 DBMS_SQL.CLOSE_CURSOR(c);
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> drop table temp;
Table dropped.
Assign date with DBMS_SQL package
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE AssignDate (expr_in IN DATE, var_inout IN OUT DATE)
2 IS
3 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
4 returnValue PLS_INTEGER;
5 BEGIN
6 DBMS_SQL.PARSE (cur, "BEGIN :var := :dtval; END;", DBMS_SQL.NATIVE);
7 DBMS_SQL.BIND_VARIABLE (cur, "var", SYSDATE);
8 DBMS_SQL.BIND_VARIABLE (cur, "dtval", expr_in);
9 returnValue := DBMS_SQL.EXECUTE (cur);
10 DBMS_SQL.VARIABLE_VALUE (cur, "var", var_inout);
11 END;
12 /
Procedure created.
SQL>
Close a cursor
SQL>
SQL> CREATE OR REPLACE PROCEDURE closecur (cur_in IN INTEGER)
2 IS
3 BEGIN
4
5 IF DBMS_SQL.IS_OPEN (cur_in);
6
7 THEN
8
9 DBMS_SQL.CLOSE_CURSOR (cur_in);
10
11 END IF;
12 END;
13 /
Warning: Procedure created with compilation errors.
SQL>
Create Pl/SQL block dynamically and then execute it by calling "DBMS_SQL.EXECUTE"
SQL>
SQL> CREATE OR REPLACE PROCEDURE assign (
2 val_in IN VARCHAR2,
3 varname_in IN VARCHAR2,
4 trc IN BOOLEAN := TRUE)
5 IS
6 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 returnValue PLS_INTEGER;
8 assign_string VARCHAR2(2000) := "BEGIN " || varname_in || " := """ || val_in || """; END;";
9 BEGIN
10 IF trc THEN DBMS_OUTPUT.PUT_LINE (assign_string); END IF;
11 DBMS_SQL.PARSE (cur, assign_string, DBMS_SQL.NATIVE);
12 returnValue := DBMS_SQL.EXECUTE (cur);
13 DBMS_SQL.CLOSE_CURSOR (cur);
14 END;
15 /
Procedure created.
SQL>
SQL>
DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE updnumval (
2 col_in IN VARCHAR2,
3 start_in IN DATE,
4 end_in IN DATE,
5 val_in IN NUMBER)
6 IS
7 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
8 returnValue PLS_INTEGER;
9 BEGIN
10 DBMS_SQL.PARSE (cur, "UPDATE emp SET " || col_in ||" = :val WHERE hiredate BETWEEN :lodate AND :hidate",DBMS_SQL.NATIVE);
11 DBMS_SQL.BIND_VARIABLE (cur, "val", val_in);
12 DBMS_SQL.BIND_VARIABLE (cur, "lodate", start_in);
13 DBMS_SQL.BIND_VARIABLE (cur, "hidate", end_in);
14 returnValue := DBMS_SQL.EXECUTE (cur);
15 DBMS_OUTPUT.PUT_LINE ("Rows updated: " || TO_CHAR (returnValue));
16 DBMS_SQL.CLOSE_CURSOR (cur);
17 END;
18 /
Procedure created.
SQL> drop table emp;
Table dropped.
DBMS_SQL.EXECUTE an update statement
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE updnumval
2 (col_in IN VARCHAR2,
3 start_in IN DATE,
4 end_in IN DATE,
5 val_in IN NUMBER)
6 IS
7 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
8 returnValue PLS_INTEGER;
9 BEGIN
10 DBMS_SQL.PARSE (cur,
11 "UPDATE emp SET " || col_in || " = " || val_in ||
12 " WHERE hiredate BETWEEN " ||
13 " TO_DATE (""" || TO_CHAR (start_in) || """)" ||
14 " AND " ||
15 " TO_DATE (""" || TO_CHAR (end_in) || """)" ,
16 DBMS_SQL.NATIVE);
17
18 returnValue := DBMS_SQL.EXECUTE (cur);
19
20 DBMS_OUTPUT.PUT_LINE ("Rows updated: " || TO_CHAR (returnValue));
21
22 DBMS_SQL.CLOSE_CURSOR (cur);
23 END;
24 /
Procedure created.
SQL> drop table emp;
Table dropped.
DBMS_SQL.LAST_ERROR_POSITION
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> DECLARE
2 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
3 errpos PLS_INTEGER;
4 mySQL VARCHAR2(200) := "SELECT ename FROM emp WHERE deptno = 10";
5 BEGIN
6 DBMS_SQL.PARSE (cur, mySQL, DBMS_SQL.NATIVE);
7 EXCEPTION
8 WHEN OTHERS
9 THEN
10 errpos := DBMS_SQL.LAST_ERROR_POSITION;
11 DBMS_OUTPUT.PUT_LINE (SQLERRM);
12 DBMS_OUTPUT.PUT_LINE (mySQL);
13 DBMS_OUTPUT.PUT_LINE (LPAD ("^", errpos, "-"));
14 DBMS_SQL.CLOSE_CURSOR (cur);
15 RAISE;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
dbms_sql.number_table
SQL>
SQL> create table t ( x int );
Table created.
SQL> declare
2 l_data dbms_sql.number_table;
3 l_empty dbms_sql.number_table;
4 begin
5 for i in 1 .. 1000
6 loop
7 l_data(mod(i,100)) := i;
8 if ( mod(i,100) = 0 )
9 then
10 forall j in 0 .. l_data.count-1
11 insert into t values ( l_data(j) );
12 l_data := l_empty;
13 end if;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
DBMS_SQL.PARSE
SQL>
SQL>
SQL> Create or replace Procedure create_dyn_table(i_region_name VARCHAR2,returnCode OUT NUMBER,errmsg OUT VARCHAR2)
2 authid current_user
3 is
4 cur_id INTEGER;
5 ret_code INTEGER;
6 sqlString VARCHAR2(1000);
7 tableName VARCHAR2(21);
8 Begin
9 tableName :="ORDERS_FOR_"||i_region_name;
10
11 sqlString :="CREATE TABLE "||tableName||
12 "(order_id NUMBER(10)PRIMARY KEY,total_price NUMBER(15,2))";
13 cur_id :=DBMS_SQL.OPEN_CURSOR;
14 DBMS_SQL.PARSE(cur_id,sqlString,DBMS_SQL.V7);
15 ret_code :=DBMS_SQL.EXECUTE(cur_id);
16 DBMS_SQL.CLOSE_CURSOR(cur_id);
17 returnCode :=0;
18 EXCEPTION WHEN OTHERS THEN
19 returnCode :=SQLCODE;
20 errmsg :="ERR:Creating table "||tableName ||"-"||SQLERRM;
21 End;
22 /
Procedure created.
SQL>
DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE updemps (
2 enametab IN DBMS_SQL.VARCHAR2_TABLE,
3 saltab IN DBMS_SQL.NUMBER_TABLE)
4 IS
5 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
6 returnValue PLS_INTEGER;
7 BEGIN
8 DBMS_SQL.PARSE (cur,"UPDATE emp SET sal = :salary WHERE ename = :employee_name", DBMS_SQL.NATIVE);
9
10 DBMS_SQL.BIND_ARRAY (cur, "salary", saltab);
11 DBMS_SQL.BIND_ARRAY (cur, "employee_name", enametab);
12
13 returnValue := DBMS_SQL.EXECUTE (cur);
14
15 DBMS_OUTPUT.PUT_LINE ("Rows updated: " || TO_CHAR (returnValue));
16
17 DBMS_SQL.CLOSE_CURSOR (cur);
18 END;
19 /
Procedure created.
SQL>
SQL> drop table emp;
Table dropped.
Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
SQL>
SQL> CREATE OR REPLACE PROCEDURE dump_long(tab IN VARCHAR2,col IN VARCHAR2, whr IN VARCHAR2 := NULL, pieces IN OUT DBMS_SQL.VARCHAR2S)
2 IS
3 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
4 returnValue PLS_INTEGER;
5
6 TYPE long_rectype IS RECORD (
7 piece_len PLS_INTEGER DEFAULT 2000,
8 pos_in_long PLS_INTEGER DEFAULT 0,
9 one_piece VARCHAR2(2000),
10 one_piece_len PLS_INTEGER
11 );
12
13 rec long_rectype;
14 BEGIN
15 DBMS_SQL.PARSE (cur,"SELECT " || col || " FROM " || tab ||" WHERE " || NVL (whr, "1 = 1"),DBMS_SQL.NATIVE);
16
17 DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);
18
19 returnValue := DBMS_SQL.EXECUTE_AND_FETCH (cur);
20
21 IF returnValue > 0
22 THEN
23 LOOP
24 DBMS_SQL.COLUMN_VALUE_LONG(cur,1,rec.piece_len,rec.pos_in_long,rec.one_piece,rec.one_piece_len);
25 EXIT WHEN rec.one_piece_len = 0;
26 pieces (NVL (pieces.LAST, 0) + 1) := rec.one_piece;
27 rec.pos_in_long := rec.pos_in_long + rec.one_piece_len;
28 END LOOP;
29 END IF;
30 DBMS_SQL.CLOSE_CURSOR (cur);
31 END;
32 /
Procedure created.
Dump query with dbms_sql
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> set echo on
SQL>
SQL> create or replace procedure dump_query( p_query in varchar2 )
2 is
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_colCnt number default 0;
6 l_cnt number default 0;
7 l_line long;
8
9 l_descTbl dbms_sql.desc_tab;
10 l_theCursor integer default dbms_sql.open_cursor;
11 begin
12 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
13 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
14 for i in 1 .. l_colCnt
15 loop
16 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
17 end loop;
18 l_status := dbms_sql.execute(l_theCursor);
19 while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
20 loop
21 l_cnt := l_cnt+1;
22 l_line := l_cnt;
23 for i in 1 .. l_colCnt loop
24 dbms_sql.column_value( l_theCursor, i, l_columnValue );
25 l_line := l_line || "," || l_columnValue;
26 end loop;
27 dbms_output.put_line( l_line );
28 end loop;
29 dbms_sql.close_cursor(l_theCursor);
30 exception
31 when others then
32 dbms_sql.close_cursor( l_theCursor );
33 raise;
34 end dump_query;
35 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> exec dump_query( "select rowid, empno, ename from emp" );
1,AAAFGQAABAAAKZKAAA,7369,SMITH
2,AAAFGQAABAAAKZKAAB,7499,ALLEN
3,AAAFGQAABAAAKZKAAC,7521,WARD
4,AAAFGQAABAAAKZKAAD,7566,JONES
5,AAAFGQAABAAAKZKAAE,7654,MARTIN
6,AAAFGQAABAAAKZKAAF,7698,BLAKE
7,AAAFGQAABAAAKZKAAG,7782,CLARK
8,AAAFGQAABAAAKZKAAH,7788,SCOTT
9,AAAFGQAABAAAKZKAAI,7839,KING
10,AAAFGQAABAAAKZKAAJ,7844,TURNER
11,AAAFGQAABAAAKZKAAK,7876,ADAMS
12,AAAFGQAABAAAKZKAAL,7900,JAMES
13,AAAFGQAABAAAKZKAAM,7902,FORD
14,AAAFGQAABAAAKZKAAN,7934,MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Package for running dynamic sql based on DBMS_SQL package
SQL> CREATE OR REPLACE PACKAGE dynsql
2 IS
3 PROCEDURE execute (cur IN INTEGER);
4 FUNCTION execute (cur IN INTEGER) RETURN INTEGER;
5 END dynsql;
6 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY dynsql
2 IS
3 PROCEDURE execute (cur IN INTEGER)
4 IS
5 feedback INTEGER;
6 BEGIN
7 feedback := DBMS_SQL.EXECUTE (cur);
8 END;
9
10 FUNCTION execute (cur IN INTEGER) RETURN INTEGER
11 IS
12 BEGIN
13 RETURN DBMS_SQL.EXECUTE (cur);
14 END;
15
16 END dynsql;
17 /
Package body created.
SQL>
SQL>
Print table with dynamic query
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER is
2 l_theCursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_descTbl dbms_sql.desc_tab;
6 l_colCnt number;
7 begin
8 dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
9
10 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
11
12 for i in 1 .. l_colCnt loop
13 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
14 end loop;
15
16 l_status := dbms_sql.execute(l_theCursor);
17
18 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
19 for i in 1 .. l_colCnt loop
20
21 dbms_sql.column_value( l_theCursor, i, l_columnValue );
22
23 dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )|| ": " ||l_columnValue );
24 end loop;
25 end loop;
26 exception
27 when others then dbms_sql.close_cursor( l_theCursor ); RAISE;
28 end;
29 /
Procedure created.
SQL>
SQL> exec print_table("select * from dept")
DEPTNO : 10
DNAME : ACCOUNTING
LOC : NEW YORK
DEPTNO : 20
DNAME : RESEARCH
LOC : DALLAS
DEPTNO : 30
DNAME : SALES
LOC : CHICAGO
DEPTNO : 40
DNAME : OPERATIONS
LOC : BOSTON
DEPTNO : 10
DNAME : ACCOUNTING
LOC : NEW YORK
DEPTNO : 20
DNAME : RESEARCH
LOC : DALLAS
DEPTNO : 30
DNAME : SALES
LOC : CHICAGO
DEPTNO : 40
DNAME : OPERATIONS
LOC : BOSTON
PL/SQL procedure successfully completed.
SQL>
SQL> drop table dept;
Table dropped.
Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
SQL>
SQL> CREATE OR REPLACE FUNCTION math_calc(p_statement_txt VARCHAR2,
2 p_precision_num PLS_INTEGER := 2)
3 RETURN NUMBER IS
4 lv_cursor_id_num PLS_INTEGER;
5 lv_statement_txt VARCHAR2(500);
6 lv_rowcount_num PLS_INTEGER;
7 lv_return_value_num NUMBER;
8 BEGIN
9 lv_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
10 lv_statement_txt := "BEGIN " ||" :lv_value_num := " || p_statement_txt || ";" ||"END;";
11 DBMS_SQL.PARSE(lv_cursor_id_num, lv_statement_txt,DBMS_SQL.NATIVE);
12 DBMS_SQL.BIND_VARIABLE(lv_cursor_id_num, ":lv_value_num", lv_return_value_num);
13
14 lv_rowcount_num := DBMS_SQL.EXECUTE(lv_cursor_id_num);
15
16 DBMS_SQL.VARIABLE_VALUE(lv_cursor_id_num, ":lv_value_num",
17 lv_return_value_num);
18 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
19 RETURN ROUND(lv_return_value_num, p_precision_num);
20 EXCEPTION
21 WHEN OTHERS THEN
22 IF DBMS_SQL.IS_OPEN(lv_cursor_id_num) THEN
23 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
24 END IF;
25 RAISE_APPLICATION_ERROR(-20101, "Error processing SQL " ||
26 "statement in MATH_CALC procedure", FALSE);
27 END math_calc;
28 /
Function created.
SQL>
SQL> DECLARE
2 lv_return_num NUMBER;
3 lv_value_num_1 NUMBER;
4 lv_value_num_2 NUMBER;
5 BEGIN
6 lv_value_num_1 := 100.002;
7 lv_value_num_2 := 3.02;
8 lv_return_num := math_calc(TO_CHAR(lv_value_num_1) ||" * " || TO_CHAR(lv_value_num_2), 5);
9 DBMS_OUTPUT.PUT_LINE("Value: " || lv_return_num);
10 END;
11 /
Value: 302.00604
PL/SQL procedure successfully completed.
SQL>
Use dbms_sql.describe_columns
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> set echo on
SQL>
SQL> create or replace procedure desc_query( p_query in varchar2 )
2 is
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_colCnt number default 0;
6 l_cnt number default 0;
7 l_line long;
8 l_descTbl dbms_sql.desc_tab;
9 l_theCursor integer default dbms_sql.open_cursor;
10 begin
11 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
12 dbms_sql.describe_columns( c => l_theCursor,
13 col_cnt => l_colCnt,
14 desc_t => l_descTbl );
15
16 for i in 1 .. l_colCnt
17 loop
18 dbms_output.put_line( "Column Type:" || l_descTbl(i).col_type );
19 dbms_output.put_line( "Max Length:" || l_descTbl(i).col_max_len );
20 dbms_output.put_line( "Name:" || l_descTbl(i).col_name );
21 dbms_output.put_line( "Name Length:" || l_descTbl(i).col_name_len );
22 dbms_output.put_line( "ObjColumn Schema Name." || l_descTbl(i).col_schema_name );
23 dbms_output.put_line( "Schema Name Length:" || l_descTbl(i).col_schema_name_len );
24 dbms_output.put_line( "Precision:" || l_descTbl(i).col_precision );
25 dbms_output.put_line( "Scale:" || l_descTbl(i).col_scale );
26 dbms_output.put_line( "Charsetid:" || l_descTbl(i).col_Charsetid );
27 dbms_output.put_line( "Charset Form:" || l_descTbl(i).col_charsetform );
28 if ( l_desctbl(i).col_null_ok ) then
29 dbms_output.put_line( "Nullable:Y" );
30 else
31 dbms_output.put_line( "Nullable:N" );
32 end if;
33 end loop;
34 dbms_sql.close_cursor(l_theCursor);
35 exception
36 when others then dbms_sql.close_cursor( l_theCursor );
37 raise;
38 end desc_query;
39 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> exec desc_query( "select rowid, ename from emp" );
Column Type:11
Max Length:16
Name:ROWID
Name Length:5
ObjColumn Schema Name.
Schema Name Length:0
Precision:0
Scale:0
Charsetid:0
Charset Form:0
Nullable:N
Column Type:1
Max Length:10
Name:ENAME
Name Length:5
ObjColumn Schema Name.
Schema Name Length:0
Precision:0
Scale:0
Charsetid:178
Charset Form:1
Nullable:Y
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Use DBMS_SQL package to parse math expression
SQL>
SQL> CREATE OR REPLACE FUNCTION math_calc(p_statement_txt VARCHAR2,
2 p_precision_num PLS_INTEGER := 2)
3 RETURN NUMBER IS
4 lv_cursor_id_num PLS_INTEGER;
5 lv_statement_txt VARCHAR2(500);
6 lv_rowcount_num PLS_INTEGER;
7 lv_return_value_num NUMBER;
8 BEGIN
9 lv_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
10 lv_statement_txt :=
11 "BEGIN " ||
12 " :lv_value_num := " || p_statement_txt || ";" ||
13 "END;";
14
15 DBMS_SQL.PARSE(lv_cursor_id_num, lv_statement_txt,DBMS_SQL.NATIVE);
16
17 DBMS_SQL.BIND_VARIABLE(lv_cursor_id_num, ":lv_value_num", lv_return_value_num);
18
19 lv_rowcount_num := DBMS_SQL.EXECUTE(lv_cursor_id_num);
20
21 DBMS_SQL.VARIABLE_VALUE(lv_cursor_id_num, ":lv_value_num",lv_return_value_num);
22 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
23 RETURN ROUND(lv_return_value_num, p_precision_num);
24 EXCEPTION
25 WHEN OTHERS THEN
26 IF DBMS_SQL.IS_OPEN(lv_cursor_id_num) THEN
27 DBMS_SQL.CLOSE_CURSOR(lv_cursor_id_num);
28 END IF;
29 RAISE_APPLICATION_ERROR(-20101, "Error processing SQL " ||
30 "statement in MATH_CALC procedure", FALSE);
31 END math_calc;
32 /
Function created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 lv_return_num NUMBER;
3 BEGIN
4 lv_return_num := math_calc("4*5", 2);
5 DBMS_OUTPUT.PUT_LINE("Value: " || lv_return_num);
6 END;
7 /
Value: 20
PL/SQL procedure successfully completed.
SQL>
Use DBMS_SQL to update a table and get the number of rows updated
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE updnumval (
2 col_in IN VARCHAR2,
3 val_in IN NUMBER,
4 where_in IN VARCHAR2 := NULL)
5 IS
6 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 returnValue PLS_INTEGER;
8 BEGIN
9 DBMS_SQL.PARSE (cur, "UPDATE emp SET " ||col_in ||" = :val " || NVL (where_in, "WHERE 1=1"),DBMS_SQL.NATIVE);
10
11 DBMS_SQL.BIND_VARIABLE (cur, "val", val_in);
12
13 returnValue := DBMS_SQL.EXECUTE (cur);
14
15 DBMS_OUTPUT.PUT_LINE ("Rows updated: " || TO_CHAR (returnValue));
16 DBMS_SQL.CLOSE_CURSOR (cur);
17 END;
18 /
Procedure created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Use dynamic SQL to check the business logic
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kate", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Dillon", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE update_salary (empid_in IN INTEGER, newsal_in IN NUMBER)
2 IS
3 updcur INTEGER := DBMS_SQL.OPEN_CURSOR;
4 returnValue INTEGER;
5 BEGIN
6 DBMS_SQL.PARSE (updcur,"UPDATE employee SET salary = GREATEST (:minsal, salary) WHERE employee_id = :empid",DBMS_SQL.NATIVE);
7 DBMS_SQL.BIND_VARIABLE (updcur, "empid", empid_in);
8 DBMS_SQL.BIND_VARIABLE (updcur, "minsal", newsal_in);
9 returnValue := DBMS_SQL.EXECUTE (updcur);
10 DBMS_SQL.CLOSE_CURSOR (updcur);
11 END;
12 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE ensure_minsal (minsal_in IN NUMBER, where_in IN VARCHAR2 := NULL)
2 IS
3 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
4 rec employee%ROWTYPE;
5 returnValue INTEGER;
6 BEGIN
7 DBMS_SQL.PARSE (cur,"SELECT employee_id, salary FROM employee WHERE " || NVL (where_in, "1=1"),DBMS_SQL.NATIVE);
8
9 DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
10 DBMS_SQL.DEFINE_COLUMN (cur, 2, 1);
11
12 returnValue := DBMS_SQL.EXECUTE (cur);
13 LOOP
14 EXIT WHEN DBMS_SQL.LAST_ROW_COUNT > 10 OR DBMS_SQL.FETCH_ROWS (cur) = 0;
15 DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
16 DBMS_SQL.COLUMN_VALUE (cur, 2, rec.salary);
17 update_salary (rec.employee_id, minsal_in);
18 DBMS_OUTPUT.PUT_LINE ("Updated salary for " || rec.employee_id);
19 END LOOP;
20
21 DBMS_SQL.CLOSE_CURSOR (cur);
22 END;
23 /
SQL>
SQL> drop table employees;
SQL>
SQL>
Wrap dbms_sql package
SQL>
SQL> create or replace package demo_pkg
2 as
3 procedure parse_bind_execute_close( p_input in varchar2 );
4 procedure bind_execute( p_input in varchar2 );
5 end;
6 /
Package created.
SQL>
SQL>
SQL> create or replace package body demo_pkg
2 as
3 g_first_time boolean := TRUE;
4 g_cursor number;
5
6 procedure parse_bind_execute_close( p_input in varchar2 )
7 as
8 l_cursor number;
9 strValue varchar2(4000);
10 returnValue number;
11 begin
12 l_cursor := dbms_sql.open_cursor;
13 dbms_sql.parse( l_cursor,"select * from dual where dummy = :x",dbms_sql.native );
14
15 dbms_sql.bind_variable( l_cursor, ":x", p_input );
16
17 dbms_sql.define_column( l_cursor, 1, strValue, 4000 );
18
19 returnValue := dbms_sql.execute( l_cursor );
20
21 if ( dbms_sql.fetch_rows( l_cursor ) <= 0 )
22
23 then
24
25 strValue := null;
26
27 else
28
29 dbms_sql.column_value( l_cursor, 1, strValue );
30
31 end if;
32
33 dbms_sql.close_cursor( l_cursor );
34
35 end parse_bind_execute_close;
36
37 procedure bind_execute( p_input in varchar2 )
38 as
39 strValue varchar2(4000);
40 returnValue number;
41 begin
42 if ( g_first_Time )
43 then
44
45 g_cursor := dbms_sql.open_cursor;
46
47 dbms_sql.parse( g_cursor,"select * from dual where dummy = :x",dbms_sql.native );
48
49 dbms_sql.define_column( g_cursor, 1, strValue, 4000 );
50
51 g_first_time := FALSE;
52
53 end if;
54
55 dbms_sql.bind_variable( g_cursor, ":x", p_input );
56
57 returnValue := dbms_sql.execute( g_cursor );
58
59 if ( dbms_sql.fetch_rows( g_cursor ) <= 0 )
60
61 then
62
63 strValue := null;
64
65 else
66
67 dbms_sql.column_value( g_cursor, 1, strValue );
68
69 end if;
70 end bind_execute;
71
72 end;
73 /
Package body created.
SQL>