Oracle PL/SQL/System Packages/dbms sql
Содержание
- 1 Call dbms_sql.describe_columns2 to get info for a column
- 2 Calling a function which uses dynamic SQL from within an SQL statement.
- 3 DBMS_SQL.BIND_VARIABLE_CHAR
- 4 DBMS_SQL.NATIVE;
- 5 DBMS_SQL package: dynamic SQL
- 6 DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
- 7 dbms_sql.varchar2_table type variable
- 8 drop the supplied table using dynamic SQL.
- 9 Dynamic select statement and row fetch
- 10 Dynamic sql statement with variable binding
- 11 Execute PL/SQL block
- 12 Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
- 13 Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
- 14 First DBMS_SQL Example
- 15 illustrates a DDL statement which is built dynamically from the procedure parameters.
- 16 Illustrates the interaction of roles and dynamic SQL.
- 17 illustrate the importance of setting out_value_size.
- 18 Non-Query DML and DDL Statements
- 19 Pass a query statement to a stored procedure
- 20 Pass where clause to a stored procedure
- 21 Use dbms_sql.bind_array to bind array to a cursor
- 22 Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
- 23 Use dbms_sql.bind_variable to bind variable
- 24 Use dbms_sql.open_cursor create a cursor
- 25 Use dbms_sql.parse to bind a select statement to a cursor
- 26 Use dbms_sql to process query, cursor and value
- 27 Use DBMS_SQL with the RETURNING clause.
Call dbms_sql.describe_columns2 to get info for a column
SQL>
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_ignore number;
4 l_desc dbms_sql.desc_tab2;
5 l_cnt number;
6 begin
7 dbms_sql.parse( l_cursor,
8 "select to_char( sysdate, ""DD-MON-YYYY"" ) || " ||
9 "to_char( sysdate, ""HH24:MI:SS"" ) " ||
10 "from dual",
11 dbms_sql.native );
12
13 dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
14
15 for i in 1 .. l_cnt loop
16 dbms_output.put_line( "Column " || i || " is "" || l_desc(i).col_name || """ );
17 end loop;
18
19 dbms_sql.close_cursor( l_cursor );
20 end;
21 /
Column 1 is "TO_CHAR(SYSDATE,"DD-MON-YYYY")||TO_CHAR(SYSDATE,"HH24:MI:SS")"
PL/SQL procedure successfully completed.
SQL> --
Calling a function which uses dynamic SQL from within an SQL statement.
SQL>
SQL> CREATE OR REPLACE FUNCTION DynamicSysdate
2 RETURN DATE AS
3
4 v_SQLStatement VARCHAR2(100) := "SELECT SYSDATE FROM dual";
5 v_CursorID INTEGER;
6 v_ReturnVal DATE;
7 v_Dummy INTEGER;
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE);
11 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_ReturnVal);
12 v_Dummy := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
13 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_ReturnVal);
14 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
15 RETURN v_ReturnVal;
16 EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
19 RAISE;
20 END DynamicSysdate;
21 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> SELECT DynamicSysdate FROM dual;
DYNAMICSYSDATE
---------------------
Jun 19, 2008 20:39:02
1 row selected.
SQL>
SQL>
DBMS_SQL.BIND_VARIABLE_CHAR
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE updateSession(
2 p_Department IN session.department%TYPE,
3 p_NewCredits IN session.num_credits%TYPE,
4 p_RowsUpdated OUT INTEGER) AS
5
6 v_CursorID INTEGER;
7 v_UpdateStmt VARCHAR2(100);
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10
11 v_UpdateStmt :=
12 "UPDATE session
13 SET num_credits = :nc
14 WHERE department = :dept";
15
16 DBMS_SQL.PARSE(v_CursorID, v_UpdateStmt, DBMS_SQL.NATIVE);
17
18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":nc", p_NewCredits);
19
20 DBMS_SQL.BIND_VARIABLE_CHAR(v_CursorID, ":dept", p_Department);
21
22 p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID);
23
24 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
28 RAISE;
29 END updateSession;
30 /
Procedure created.
SQL>
SQL> drop table session;
Table dropped.
SQL>
DBMS_SQL.NATIVE;
SQL>
SQL> CREATE OR REPLACE PROCEDURE CreateDynamicTables(
2 p_Method IN VARCHAR2)
3 AUTHID CURRENT_USER AS
4 v_CreateString1 VARCHAR2(100) :=
5 "CREATE TABLE dbms_sql_table (f1 NUMBER)";
6 v_CreateString2 VARCHAR2(100) :=
7 "CREATE TABLE native_table (f1 NUMBER)";
8 v_Dummy INTEGER;
9 v_CursorID INTEGER;
10 BEGIN
11 IF p_Method = "DBMS_SQL" THEN
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
14 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
15 ELSE
16 EXECUTE IMMEDIATE v_CreateString2;
17 END IF;
18 END CreateDynamicTables;
19 /
Procedure created.
SQL>
SQL> exec CreateDynamicTables("DBMS_SQL")
PL/SQL procedure successfully completed.
SQL> exec CreateDynamicTables("native")
PL/SQL procedure successfully completed.
SQL>
SQL> desc dbms_sql_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL> desc native_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL>
SQL> DROP TABLE dbms_sql_table;
Table dropped.
SQL> DROP TABLE native_table;
Table dropped.
SQL>
DBMS_SQL package: dynamic SQL
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE updateSession(
2 p_Department IN session.department%TYPE,
3 p_NewCredits IN session.num_credits%TYPE,
4 p_RowsUpdated OUT INTEGER) AS
5
6 v_CursorID INTEGER;
7 v_UpdateStmt VARCHAR2(100);
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10
11 v_UpdateStmt :=
12 "UPDATE session
13 SET num_credits = :nc
14 WHERE department = :dept";
15
16 DBMS_SQL.PARSE(v_CursorID, v_UpdateStmt, DBMS_SQL.NATIVE);
17
18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":nc", p_NewCredits);
19
20 DBMS_SQL.BIND_VARIABLE_CHAR(v_CursorID, ":dept", p_Department);
21
22 p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID);
23
24 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
25 EXCEPTION
26 WHEN OTHERS THEN
27 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
28 RAISE;
29 END updateSession;
30 /
Procedure created.
SQL>
SQL> drop table session;
Table dropped.
SQL>
DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_CreateTableString VARCHAR2(500);
4 v_NUMRows INTEGER;
5
6 BEGIN
7 v_CursorID := DBMS_SQL.OPEN_CURSOR;
8 v_CreateTableString := "CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50))";
9
10 DBMS_SQL.PARSE(v_CursorID,v_CreateTableString,DBMS_SQL.V7);
11 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
12
13 EXCEPTION
14 WHEN OTHERS THEN
15 IF SQLCODE != -955 THEN -- 955 is error that table exists
16 RAISE;
17 ELSE
18 DBMS_OUTPUT.PUT_LINE("Table Already Exists!");
19 END IF;
20 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> --
dbms_sql.varchar2_table type variable
SQL>
SQL>
SQL> create table myTable
2 as
3 select rownum id, a.*
4 from all_objects a
5 where 1=0
6 /
Table created.
SQL>
SQL> declare
2 l_owner dbms_sql.varchar2_table;
3 l_object_name dbms_sql.varchar2_table;
4 l_object_type dbms_sql.varchar2_table;
5 l_created dbms_sql.varchar2_table;
6
7 cursor c is
8 select owner, object_name, object_type, created
9 from myTable
10 order by created DESC;
11 begin
12 select owner, object_name, object_type, created
13 bulk collect into l_owner, l_object_name, l_object_type, l_created from ( select owner, object_name, object_type, created from myTable order by created DESC )
14 where ROWNUM <= 10;
15
16 open c;
17 fetch c bulk collect
18 into l_owner, l_object_name, l_object_type, l_created
19 limit 10;
20 close c;
21 end;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
drop the supplied table using dynamic SQL.
SQL>
SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
2 sqlString VARCHAR2(100);
3 v_Cursor BINARY_INTEGER;
4 v_ReturnCode BINARY_INTEGER;
5 BEGIN
6 sqlString := "DROP TABLE " || p_Table;
7
8 v_Cursor := DBMS_SQL.OPEN_CURSOR;
9
10 DBMS_SQL.PARSE(v_Cursor, sqlString, DBMS_SQL.NATIVE);
11 v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor);
12
13 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
14 END DropTable;
15 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE DropTable(p_Table IN VARCHAR2) AS
2 sqlString VARCHAR2(100);
3 BEGIN
4 sqlString := "DROP TABLE " || p_Table;
5
6 EXECUTE IMMEDIATE sqlString;
7 END DropTable;
8 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
Dynamic select statement and row fetch
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_SelectRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5 v_MyNum INTEGER;
6 v_MyText VARCHAR2(50);
7
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 v_SelectRecords := "SELECT * from MyTable";
11 DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
12 DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
13 DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
14
15 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
16 LOOP
17 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
18 EXIT;
19 END IF;
20
21 DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
22 DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
23
24 DBMS_OUTPUT.PUT_LINE(v_MyNum || " " || v_MyText);
25
26 END LOOP;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 RAISE;
31 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
32 END;
33 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --
Dynamic sql statement with variable binding
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_InsertRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5
6 BEGIN
7 v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
8 v_InsertRecords := "INSERT INTO MyTable(MyRow,MyDesc)VALUES (:mynum,:mytext)"; -- Write SQL to insert records
9
10 DBMS_SQL.PARSE(v_CursorID,v_InsertRecords,DBMS_SQL.V7);
11 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",1);
12 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","One");
13 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
14 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: " || v_NUMRows);
15
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",2);
17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Two");
18 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
19 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
20
21 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",3);
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Three");
23 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
24 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
25
26 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mynum",4);
27 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":mytext","Four");
28 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
29 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
30
31 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
32 DBMS_OUTPUT.PUT_LINE("The number of records just processed is: "|| v_NUMRows);
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 RAISE;
37
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 COMMIT;
40 END;
41 /
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
PL/SQL procedure successfully completed.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL>
SQL> --
Execute PL/SQL block
SQL>
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_MatchRecord VARCHAR2(500);
4 v_NUMRows INTEGER;
5 v_MyNum INTEGER;
6 v_MyText VARCHAR2(50);
7
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 v_MatchRecord := "BEGIN
11 SELECT MyRow,MyDesc
12 INTO :MyRow, :MyText FROM MyTable
13 WHERE MyRow = 2;
14 END;";
15
16 DBMS_SQL.PARSE(v_CursorID,v_MatchRecord,DBMS_SQL.V7);
17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":MyRow",v_MyNum);
18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":MyText",v_MyText,50);
19
20 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
21 DBMS_SQL.VARIABLE_VALUE(v_CursorId,":MyRow",v_MyNum);
22 DBMS_SQL.VARIABLE_VALUE(v_CursorId,":MyText",v_MyText);
23 DBMS_OUTPUT.PUT_LINE(v_MyNum || " " || v_MyText);
24 EXCEPTION
25 WHEN OTHERS THEN
26 RAISE;
27
28 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
29 END;
30 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 26
SQL>
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --
Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicPLSQL (
2 p_ID IN lecturer.ID%TYPE) IS
3
4 v_CursorID INTEGER;
5 v_BlockStr VARCHAR2(500);
6 myFirstName lecturer.first_name%TYPE;
7 v_LastName lecturer.last_name%TYPE;
8 v_Dummy INTEGER;
9
10 BEGIN
11 v_CursorID := DBMS_SQL.OPEN_CURSOR;
12
13 v_BlockStr :=
14 "BEGIN
15 SELECT first_name, last_name
16 INTO :first_name, :last_name
17 FROM lecturer
18 WHERE ID = :ID;
19 END;";
20
21 DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);
22
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":first_name", myFirstName, 30);
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":last_name", v_LastName, 30);
25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":ID", p_ID);
26
27 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
28
29 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":first_name", myFirstName);
30 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":last_name", v_LastName);
31
32 INSERT INTO MyTable (num_col, char_col)
33 VALUES (p_ID, myFirstName || " " || v_LastName);
34
35 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
36
37 COMMIT;
38 EXCEPTION
39 WHEN OTHERS THEN
40 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
41 RAISE;
42 END DynamicPLSQL;
43 /
Procedure created.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicQuery (
2 p_Major1 IN lecturer.major%TYPE DEFAULT NULL,
3 p_Major2 IN lecturer.major%TYPE DEFAULT NULL) AS
4
5 v_CursorID INTEGER;
6 v_SelectStmt VARCHAR2(500);
7 myFirstName lecturer.first_name%TYPE;
8 v_LastName lecturer.last_name%TYPE;
9 v_Major lecturer.major%TYPE;
10 v_Dummy INTEGER;
11
12 BEGIN
13 v_CursorID := DBMS_SQL.OPEN_CURSOR;
14
15 v_SelectStmt := "SELECT first_name, last_name, major
16 FROM lecturer
17 WHERE major IN (:m1, :m2)
18 ORDER BY major, last_name";
19
20 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
21
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m1", p_Major1);
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m2", p_Major2);
24
25 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, myFirstName, 20);
26 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);
27 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30);
28
29 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
30
31 LOOP
32 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
33 EXIT;
34 END IF;
35
36 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, myFirstName);
37 DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);
38 DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major);
39
40 INSERT INTO MyTable (char_col)VALUES (myFirstName || " " || v_LastName || " is a " ||v_Major || " major.");
41 END LOOP;
42
43 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
44
45 COMMIT;
46 EXCEPTION
47 WHEN OTHERS THEN
48 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
49 RAISE;
50 END DynamicQuery;
51 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table mytable;
Table dropped.
First DBMS_SQL Example
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
2 p_Description IN VARCHAR2) IS
3
4 v_Cursor NUMBER;
5 v_CreateString VARCHAR2(100);
6 v_DropString VARCHAR2(100);
7 v_NumRows INTEGER;
8 BEGIN
9 v_Cursor := DBMS_SQL.OPEN_CURSOR;
10
11 v_DropString := "DROP TABLE MyTable";
12
13 BEGIN
14 DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.V7);
15 v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
16 EXCEPTION
17 WHEN OTHERS THEN
18 IF SQLCODE != -942 THEN
19 RAISE;
20 END IF;
21 END;
22 v_CreateString := "CREATE TABLE MyTable " || p_Description;
23 DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7);
24 v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
25
26 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
27 EXCEPTION
28 WHEN OTHERS THEN
29 DBMS_SQL.CLOSE_CURSOR(v_Cursor);
30 RAISE;
31 END RecreateTempTable;
32 /
Procedure created.
SQL>
illustrates a DDL statement which is built dynamically from the procedure parameters.
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicSequence(
2 p_SequenceName IN VARCHAR2,
3 p_IncrementBy IN NUMBER := NULL,
4 p_StartWith IN NUMBER := NULL,
5 p_MaxValue IN NUMBER := NULL,
6 p_MinValue IN NUMBER := NULL,
7 p_Cycle IN VARCHAR2 := NULL,
8 p_Cache IN NUMBER := NULL) AS
9
10 v_CreateStmt VARCHAR2(200);
11 v_CursorID INTEGER;
12 BEGIN
13 v_CreateStmt := "CREATE SEQUENCE " || p_SequenceName;
14
15 IF p_IncrementBy IS NOT NULL THEN
16 v_CreateStmt :=
17 v_CreateStmt || " INCREMENT BY " || p_IncrementBy;
18 END IF;
19
20 IF p_StartWith IS NOT NULL THEN
21 v_CreateStmt :=
22 v_CreateStmt || " START WITH " || p_StartWith;
23 END IF;
24
25 IF p_MaxValue IS NOT NULL THEN
26 v_CreateStmt :=
27 v_CreateStmt || " MAXVALUE " || p_MaxValue;
28 END IF;
29
30 IF p_MinValue IS NOT NULL THEN
31 v_CreateStmt :=
32 v_CreateStmt || " MINVALUE " || p_MinValue;
33 END IF;
34
35 IF p_Cycle IS NOT NULL THEN
36 v_CreateStmt :=
37 v_CreateStmt || " " || p_Cycle || " ";
38 END IF;
39
40 IF p_Cache IS NOT NULL THEN
41 v_CreateStmt :=
42 v_CreateStmt || " CACHE " || p_Cache;
43 END IF;
44
45 -- And now execute it.
46 v_CursorID := DBMS_SQL.OPEN_CURSOR;
47 DBMS_SQL.PARSE(v_CursorID, v_CreateStmt, DBMS_SQL.NATIVE);
48 END DynamicSequence;
49 /
Procedure created.
SQL>
SQL>
SQL>
Illustrates the interaction of roles and dynamic SQL.
SQL>
SQL> DECLARE
2 v_CreateString1 VARCHAR2(100) :=
3 "CREATE TABLE dbms_sql_table (f1 NUMBER)";
4 v_CreateString2 VARCHAR2(100) :=
5 "CREATE TABLE native_table (f1 NUMBER)";
6 v_Dummy INTEGER;
7 v_CursorID INTEGER;
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
11 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
12
13 EXECUTE IMMEDIATE v_CreateString2;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> desc dbms_sql_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL> desc native_table
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------
F1 NUMBER
SQL>
SQL> DROP TABLE dbms_sql_table;
Table dropped.
SQL> DROP TABLE native_table;
Table dropped.
SQL>
illustrate the importance of setting out_value_size.
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE DynamicPLSQL (
2 p_ID IN lecturer.ID%TYPE) IS
3
4 v_CursorID INTEGER;
5 v_BlockStr VARCHAR2(500);
6 myFirstName lecturer.first_name%TYPE;
7 v_LastName lecturer.last_name%TYPE;
8 v_Dummy INTEGER;
9
10 BEGIN
11 v_CursorID := DBMS_SQL.OPEN_CURSOR;
12
13 v_BlockStr :=
14 "BEGIN
15 SELECT first_name, last_name
16 INTO :first_name, :last_name
17 FROM lecturer
18 WHERE ID = :ID;
19 END;";
20
21 DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.NATIVE);
22
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":first_name", myFirstName);
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":last_name", v_LastName);
25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":ID", p_ID);
26
27 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
28
29 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":first_name", myFirstName);
30 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":last_name", v_LastName);
31
32 DBMS_OUTPUT.PUT("ID: " || p_ID || " ");
33 DBMS_OUTPUT.PUT_LINE(myFirstName || " " || v_LastName);
34
35 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
36 EXCEPTION
37 WHEN OTHERS THEN
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 RAISE;
40 END DynamicPLSQL;
41 /
Procedure created.
SQL>
SQL> REM This will raise ORA-6502.
SQL> exec DynamicPLSQL(10010)
BEGIN DynamicPLSQL(10010); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "sqle.DYNAMICPLSQL", line 39
ORA-06512: at line 1
SQL>
SQL> drop table lecturer;
Table dropped.
Non-Query DML and DDL Statements
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE DeleteMajor(
2 p_Major IN lecturer.major%TYPE,
3 p_RowsDeleted OUT INTEGER) AS
4
5 v_CursorID INTEGER;
6 v_DeleteStmt VARCHAR2(100);
7 BEGIN
8 v_CursorID := DBMS_SQL.OPEN_CURSOR;
9
10 v_DeleteStmt := "DELETE FROM lecturer WHERE major = :m";
11
12 DBMS_SQL.PARSE(v_CursorID, v_DeleteStmt, DBMS_SQL.V7);
13
14 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":m", p_Major);
15
16 p_RowsDeleted := DBMS_SQL.EXECUTE(v_CursorID);
17
18 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
22 RAISE;
23 END DeleteMajor;
24 /
Procedure created.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Pass a query statement to a stored procedure
SQL>
SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
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
2 procedure print_table( p_query in varchar2 )
3 AUTHID CURRENT_USER is
4 l_theCursor integer default dbms_sql.open_cursor;
5 l_columnValue varchar2(4000);
6 l_status integer;
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 begin
10 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
11 dbms_sql.describe_columns
12 ( l_theCursor, l_colCnt, l_descTbl );
13
14 for i in 1 .. l_colCnt loop
15 dbms_sql.define_column
16 (l_theCursor, i, l_columnValue, 4000);
17 end loop;
18
19 l_status := dbms_sql.execute(l_theCursor);
20
21 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
22 for i in 1 .. l_colCnt loop
23 dbms_sql.column_value
24 ( l_theCursor, i, l_columnValue );
25 dbms_output.put_line
26 ( rpad( l_descTbl(i).col_name, 30 )
27 || ": " ||
28 substr( l_columnValue, 1, 200 ) );
29 end loop;
30 end loop;
31
32 end print_table;
33 /
Procedure created.
SQL>
SQL> exec print_table( "select * from emp where empno like ""778%""" );
EMPNO : 7782
ENAME : CLARK
JOB : MANAGER
MGR : 7839
HIREDATE : 09-JUN-1981 00:00:00
SAL : 2450
COMM :
DEPTNO : 10
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE : 09-DEC-1982 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL> --
Pass where clause to a stored procedure
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 );
Table created.
SQL>
SQL>
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>
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>
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>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
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>
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>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE showemps (
2 where_in IN VARCHAR2 := NULL
3 )
4 IS
5 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
6 fdbk INTEGER;
7
8 last_name_tab DBMS_SQL.VARCHAR2_TABLE;
9 hire_date_tab DBMS_SQL.DATE_TABLE;
10 BEGIN
11 DBMS_SQL.PARSE (cur,
12 "SELECT last_name, hire_date FROM employees
13 WHERE " || NVL (where_in, "1=1") ||
14 "ORDER BY hire_date",
15 DBMS_SQL.NATIVE);
16
17 DBMS_SQL.DEFINE_ARRAY (cur, 1, last_name_tab, 100, 1);
18 DBMS_SQL.DEFINE_ARRAY (cur, 2, hire_date_tab, 100, 1);
19
20 fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur);
21
22 DBMS_SQL.COLUMN_VALUE (cur, 1, last_name_tab);
23 DBMS_SQL.COLUMN_VALUE (cur, 2, hire_date_tab);
24
25 FOR rowind IN last_name_tab.FIRST .. last_name_tab.LAST
26 LOOP
27 DBMS_OUTPUT.PUT_LINE (
28 last_name_tab(rowind) ||
29 " was hired on " ||
30 hire_date_tab(rowind));
31 END LOOP;
32
33 DBMS_SQL.CLOSE_CURSOR (cur);
34 END;
35 /
Procedure created.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>
Use dbms_sql.bind_array to bind array to a cursor
SQL>
SQL>
SQL> create table foo (
2 a integer,
3 b dec( 9, 2 ),
4 c character varying( 30 ),
5 d national char( 3 )
6 )
7 /
Table created.
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_ignore number;
4 l_num dbms_sql.number_table;
5 l_var dbms_sql.varchar2_table;
6 begin
7 dbms_sql.parse( l_cursor,"insert into foo(b,c) values ( :n, :c )",dbms_sql.native );
8 l_num(1) := 3;
9 l_num(2) := 4;
10 l_var(1) := "Tom";
11 l_var(2) := "Joel";
12 dbms_sql.bind_array( l_cursor, ":N", l_num );
13 dbms_sql.bind_array( l_cursor, ":C", l_var );
14 l_ignore := dbms_sql.execute( l_cursor );
15 dbms_sql.close_cursor( l_cursor );
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> select * from foo;
A B C D
---------- ---------- ------------------------------ ---
3 Tom
4 Joel
2 rows selected.
SQL>
SQL> drop table foo;
Table dropped.
SQL>
SQL> --
Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
SQL>
SQL>
SQL> create table foo (
2 a integer,
3 b dec( 9, 2 ),
4 c character varying( 30 ),
5 d national char( 3 )
6 )
7 /
SQL>
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_ignore number;
4 begin
5 dbms_sql.parse( l_cursor,"insert into foo values ( :n, :c )",dbms_sql.native );
6 dbms_sql.bind_variable( l_cursor, ":N", 1 );
7 dbms_sql.bind_variable( l_cursor, ":C", "Chris" );
8 l_ignore := dbms_sql.execute( l_cursor );
9 dbms_sql.bind_variable( l_cursor, ":N", 2 );
10 dbms_sql.bind_variable( l_cursor, ":C", "Sean" );
11 l_ignore := dbms_sql.execute( l_cursor );
12 dbms_sql.close_cursor( l_cursor );
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select * from foo;
N
----------
V
----------------------------------------------------------------------------------------------------
1
Chris
2
Sean
2 rows selected.
SQL>
SQL> drop table foo;
Table dropped.
SQL> --
Use dbms_sql.bind_variable to bind variable
SQL>
SQL> create table myTable( pid primary key )
2 organization index
3 as select rownum from all_objects
4 where rownum <=0;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure binding is
2 cursorValue number;
3
4 returnValue number;
5 returnValue1 number;
6 begin
7 cursorValue := dbms_sql.open_cursor;
8 dbms_sql.parse(cursorValue, "select pid from myTable "|| "where pid = :b1", dbms_sql.native);
9 for i in 1 ..0 loop
10 dbms_sql.bind_variable(cursorValue,":b1",i);
11 returnValue := dbms_sql.execute(cursorValue);
12 returnValue1 := dbms_sql.fetch_rows(cursorValue);
13 end loop;
14 dbms_sql.close_cursor(cursorValue);
15 end;
16 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>
Use dbms_sql.open_cursor create a cursor
SQL>
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_stmt dbms_sql.varchar2s;
4 begin
5 l_stmt(3) := "junk";
6 l_stmt(4) := "create table foo";
7 l_stmt(5) := "( n numb";
8 l_stmt(6) := "er, v varchar2(100)";
9 l_stmt(7) := ")";
10 l_stmt(8) := "more junk";
11 dbms_sql.parse( l_cursor,
12 l_stmt,
13 4,
14 7,
15 FALSE,
16 dbms_sql.native );
17 dbms_sql.close_cursor( l_cursor );
18 end;
19 /
PL/SQL procedure successfully completed.
SQL> --
Use dbms_sql.parse to bind a select statement to a cursor
SQL>
SQL>
SQL> create table foo (
2 a integer,
3 b dec( 9, 2 ),
4 c character varying( 30 ),
5 d national char( 3 )
6 )
7 /
Table created.
SQL> declare
2 l_cursor number := dbms_sql.open_cursor;
3 l_num dbms_sql.number_table;
4 l_var dbms_sql.varchar2_table;
5 l_ignore number;
6 l_cnt number;
7 begin
8 dbms_sql.parse( l_cursor,"select b, c from foo",dbms_sql.native );
9 dbms_sql.define_array( l_cursor, 1, l_num, 100, 1 );
10 dbms_sql.define_array( l_cursor, 2, l_var, 100, 1 );
11
12 l_ignore := dbms_sql.execute( l_cursor );
13 loop
14 dbms_output.put_line( "About to attempt a fetch of 100 rows." );
15 l_cnt := dbms_sql.fetch_rows( l_cursor );
16 dbms_output.put_line( "Fetched " || l_cnt || " rows." );
17
18 exit when l_cnt = 0;
19
20 dbms_sql.column_value( l_cursor, 1, l_num );
21 dbms_sql.column_value( l_cursor, 2, l_var );
22
23 for i in 1 .. l_num.count loop
24 dbms_output.put_line( "N = " || l_num(i) || " V = " || l_var(i) );
25 end loop;
26
27 exit when l_cnt < 100;
28
29 end loop;
30 dbms_sql.close_cursor( l_cursor );
31 end;
32 /
About to attempt a fetch of 100 rows.
Fetched 0 rows.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table foo;
Table dropped.
SQL> --
Use dbms_sql to process query, cursor and value
SQL> create table myTable ( x number ) pctfree 0;
Table created.
SQL>
SQL> insert into myTable select rownum from all_objects where rownum < 100;
SQL>
SQL> create or replace procedure ARRAY_PROCESS is
2 s integer := dbms_sql.open_cursor;
3 n1 dbms_sql.number_table;
4 d number;
5 c number;
6 BEGIN
7 dbms_sql.parse(s,"select * from myTable", DBMS_SQL.native);
8 dbms_sql.define_array(s,1,n1,500,1);
9 d := dbms_sql.execute(s);
10 loop
11 c := DBMS_SQL.FETCH_ROWS(s);
12 DBMS_SQL.COLUMN_VALUE(s, 1, n1);
13 exit when c < 500;
14 end loop;
15
16 DBMS_SQL.CLOSE_CURSOR(s);
17 END;
18 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Use DBMS_SQL with the RETURNING clause.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_NumVal NUMBER := 1;
4 v_StringVal VARCHAR2(20) := "rowid";
5 BEGIN
6 INSERT INTO MyTable VALUES (v_NumVal, v_StringVal)
7 RETURNING ROWID INTO v_RowidString;
8 DBMS_OUTPUT.PUT_LINE("ROWID of new row is: " || v_RowidString);
9 END;
10 /
ROWID of new row is: AAADlMAABAAAKV6AAA
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 v_RowidString VARCHAR2(100);
3 v_CursorID INTEGER;
4 v_NumVal NUMBER := 1;
5 v_StringVal VARCHAR2(20) := "rowid";
6 sqlString VARCHAR2(100);
7 v_ReturnCode NUMBER;
8 BEGIN
9 sqlString :=
10 "INSERT INTO MyTable VALUES (:n, :c) " ||
11 "RETURNING ROWID INTO :r";
12 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.PARSE(v_CursorID, sqlString, DBMS_SQL.NATIVE);
14 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":n", v_NumVal);
15 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":c", v_StringVal);
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":r", v_RowidString, 100);
17 v_ReturnCode := DBMS_SQL.EXECUTE(v_CursorID);
18 DBMS_SQL.VARIABLE_VALUE(v_CursorID, ":r", v_RowidString);
19 DBMS_OUTPUT.PUT_LINE("ROWID of new row is: "|| v_RowidString);
20 END;
21 /
ROWID of new row is: AAADlMAABAAAKV6AAB
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>