◉ PL : Procedural Logic.
◉ SQL : Structured Query Language
The two are meshed together so well it is easy to forget this separation exists. One area where the separation does show is in the differing support data types. In previous database versions, binding of PL/SQL-only data types from JDBC, OCI, static and native dynamic SQL was a problem. Oracle 12c now supports the binding of additional PL/SQL-only data types to anonymous blocks, PL/SQL function calls in SQL, the TABLE operator in SQL and CALL statements.
There are some restrictions associated with this functionality.
◉ The PL/SQL-only data type must be a built-in type, or defined in a package specification.
◉ For associative arrays, only the INDEX BY PLS_INTEGER for us currently supported.
◉ PL/SQL functions called in SQL can have PL/SQL-only data types bound as input parameters, but they can not return PL/SQL-only data types as SQL does not understand them. Even if they are defined in the package specification.
◉ SQL still does not understand BOOLEAN types, so a BOOLEAN variable can be bound into an input parameter to a PL/SQL function call in a SQL statement, but BOOLEAN literals (TRUE, FALSE, or NULL) cannot be used directly.
The examples in the documentation are very good, but I will repeat similar tests here to make sure they work as described.
For most PL/SQL programmers, the biggest impact of this change will be be seen in dynamic SQL.
◉ BOOLEAN Types
The following code shows an example of binding a BOOLEAN type as an in parameter to a PL/SQL function, called in a dynamic SQL statement.
CREATE OR REPLACE FUNCTION boolean_test (p_boolean IN BOOLEAN)
RETURN VARCHAR2 AUTHID DEFINER AS
BEGIN
IF p_boolean THEN
RETURN 'TRUE';
ELSE
RETURN 'FALSE';
END IF;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_boolean BOOLEAN := TRUE;
l_result VARCHAR2(10);
BEGIN
l_sql := 'SELECT boolean_test(:l_boolean) INTO :l_result FROM dual';
EXECUTE IMMEDIATE l_sql INTO l_result USING l_boolean;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
l_result=TRUE
PL/SQL procedure successfully completed.
SQL>
Notice what happens if we try to substitute the bound BOOLEAN type for a BOOLEAN literal.
DECLARE
l_sql VARCHAR2(32767);
l_result VARCHAR2(10);
BEGIN
l_sql := 'SELECT boolean_test(TRUE) INTO :l_result FROM dual';
EXECUTE IMMEDIATE l_sql INTO l_result;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
*
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier
ORA-06512: at line 6
SQL>
◉ Record Types
The following code shows an example of binding record types in an anonymous blocked called from dynamic SQL.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS
TYPE rec_type IS RECORD (id NUMBER, description VARCHAR2(50));
PROCEDURE record_test (p_rec OUT rec_type);
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE record_test (p_rec OUT rec_type) AS
BEGIN
p_rec.id := 1;
p_rec.description := 'ONE';
END;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_record test_pkg.rec_type;
BEGIN
l_sql := 'BEGIN test_pkg.record_test(:l_record); END;';
EXECUTE IMMEDIATE l_sql USING OUT l_record;
DBMS_OUTPUT.PUT_LINE('l_record.id = ' || l_record.id);
DBMS_OUTPUT.PUT_LINE('l_record.description = ' || l_record.description);
END;
/
l_record.id = 1
l_record.description = ONE
PL/SQL procedure successfully completed.
SQL>
◉ Collections
The following code shows an asociative array being bound. Currently, only associative arrays using INDEX BY PLS_INTEGER can be bound in this way, not those using INDEX BY VARCHAR2.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS
TYPE collection_type IS TABLE OF VARCHAR2(10)
INDEX BY PLS_INTEGER;
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER IS
BEGIN
FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
END LOOP;
RETURN p_collection.COUNT;
END;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_collection test_pkg.collection_type;
l_result NUMBER;
BEGIN
l_collection(1) := 'ONE';
l_collection(2) := 'TWO';
l_collection(3) := 'THREE';
l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3
PL/SQL procedure successfully completed.
SQL>
The following example is similar to the previous associative array example, but it uses a nested table type.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS
TYPE collection_type IS TABLE OF VARCHAR2(10);
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER IS
BEGIN
FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
END LOOP;
RETURN p_collection.COUNT;
END;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_collection test_pkg.collection_type;
l_result NUMBER;
BEGIN
l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');
l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3
PL/SQL procedure successfully completed.
SQL>
The VARRAY example below is very similar to that for nested tables.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS
TYPE collection_type IS VARRAY(10) OF VARCHAR2(10);
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN NUMBER IS
BEGIN
FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
END LOOP;
RETURN p_collection.COUNT;
END;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_collection test_pkg.collection_type;
l_result NUMBER;
BEGIN
l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');
l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3
PL/SQL procedure successfully completed.
SQL>
◉ TABLE Operator
The following code defines a pipelined table function that accepts a PL/SQL collection and pipes its contents out a rows, so it can be queried using the TABLE operator.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS
TYPE collection_type IS TABLE OF VARCHAR2(10);
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN collection_type PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
FUNCTION display_collection_contents (p_collection IN collection_type)
RETURN collection_type PIPELINED IS
BEGIN
FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
PIPE ROW (p_collection(i));
END LOOP;
RETURN;
END;
END;
/
SET SERVEROUTPUT ON
DECLARE
l_sql VARCHAR2(32767);
l_collection test_pkg.collection_type;
l_result NUMBER;
BEGIN
l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');
l_sql := 'SELECT COUNT(*) INTO :l_result FROM TABLE(test_pkg.display_collection_contents(:l_collection))';
EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
l_result=3
PL/SQL procedure successfully completed.
SQL>
Posts
0 comments:
Post a Comment