一、Executing DDL and SCL Statements in PL/SQL

-- Create an object t_emp and a datatype t_emplist as a table of type t_emp
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/
-- Create a table with a nested table of type t_emplist
CREATE TABLE dept_new (id NUMBER, emps t_emplist)
  NESTED TABLE emps STORE AS emp_table;
-- Populate the dept_new table with data
INSERT INTO dept_new VALUES 
(
  10, 
  t_emplist
  (
    t_emp(1, 'SCOTT'),
    t_emp(2, 'BRUCE')
  )
);
-- Write a PL/SQL block that queries table dept_new and nested table emps
-- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL
DECLARE
  v_deptid  NUMBER;
  v_ename   VARCHAR2(20);
BEGIN
  EXECUTE IMMEDIATE 'SELECT d.id, e.name
                     FROM dept_new d, TABLE(d.emps) e 
                     WHERE e.id = 1'
  INTO v_deptid, v_ename;
END;
/

二、Executing Dynamic Queries

CREATE OR REPLACE PROCEDURE query_invoice
  (p_month VARCHAR2, p_year VARCHAR2)
IS
  TYPE cur_typ IS REF CURSOR;
  v_inv_cursor cur_typ; -- Declare a cursor variable
  v_inv_query  VARCHAR2(200);
  v_inv_num    NUMBER;
  v_inv_cust   VARCHAR2(20);
  v_inv_amt    NUMBER;
BEGIN
  -- Write dynamic query receiving month and year as parameters
  -- and using these values to form. the table name, for example, inv_APR_2004
  v_inv_query := 'SELECT num, cust, amt FROM inv_' 
                 || p_month 
                 || '_'
                 || p_year 
                 || ' WHERE v_inv_num = :g_id';  --可以使任意值，比如:1
  -- Open a cursor variable
  OPEN v_inv_cursor FOR v_inv_query USING v_inv_num;
  -- Fetch row into variables
    LOOP
      FETCH v_inv_cursor 
        INTO v_inv_num, v_inv_cust, v_inv_amt;
      EXIT WHEN v_inv_cursor%NOTFOUND;
      -- process row here
    END LOOP;
  CLOSE v_inv_cursor;
END;
/

三、Optimizing Execution Dynamically

CREATE OR REPLACE PROCEDURE query_emp
  (p_hint VARCHAR2) 
IS
  TYPE cur_typ IS REF CURSOR;
  v_emp_cursor cur_typ;
BEGIN
  OPEN v_emp_cursor FOR 'SELECT '
                        || p_hint 
                        ||' empno, ename, sal, job FROM emp WHERE empno = 7566';  -- process ...
  CLOSE v_emp_cursor;
END;
/

the user can pass values such as the following for p_hint:
p_hint = '/*+ ALL_ROWS */'
p_hint = '/*+ FIRST_ROWS */'
p_hint = '/*+ CHOOSE */'

四、Executing Dynamic PL/SQL Blocks

CREATE OR REPLACE PROCEDURE event_handler_1
(p_handle NUMBER)
IS
BEGIN   -- process event 1 RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_2
(p_handle NUMBER)
IS
BEGIN   -- process event 2 RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_3
(p_handle NUMBER)
IS
BEGIN   -- process event 3
RETURN;
END;
/

CREATE OR REPLACE PROCEDURE event_dispatcher
(p_event_num NUMBER, p_handle NUMBER)
IS
BEGIN
IF (p_event_num = 1) THEN
    EVENT_HANDLER_1(p_handle);
ELSIF (p_event_num = 2) THEN
    EVENT_HANDLER_2(p_handle);
ELSIF (p_event_num = 3) THEN
    EVENT_HANDLER_3(p_handle);
END IF;
END;
/

By using native dynamic SQL, you can write a smaller, more flexible event dispatcher as shown in the following example.

CREATE OR REPLACE PROCEDURE event_dispatcher
   (p_event_num NUMBER, p_handle NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE
    'BEGIN
       EVENT_HANDLER_' || TO_CHAR(p_event_num) || '(:1);
     END;'
USING p_handle;
END;
/

五、Developing with Native Dynamic SQL:

CREATE OR REPLACE PROCEDURE salary_raise
(p_raise_percent NUMBER, p_job VARCHAR2)
IS TYPE loc_array_type IS TABLE OF VARCHAR2(40)
    INDEX BY binary_integer;
v_dml_str VARCHAR2            (200);
v_loc_array    loc_array_type;
BEGIN
-- bulk fetch the list of office locations
SELECT location BULK COLLECT
    INTO v_loc_array
FROM offices;
-- for each location, give a raise to employees with the given 'job'
FOR i IN v_loc_array.first..v_loc_array.last LOOP
    v_dml_str := 'UPDATE emp_'
                 || v_loc_array(i)
                 || ' SET sal = sal * (1+(:p_raise_percent/100))'
                 || ' WHERE p_job = :g_job_title';
    EXECUTE IMMEDIATE v_dml_str USING p_raise_percent, p_job;
END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE drop_location
(p_loc VARCHAR2)
IS
BEGIN
-- Drop the employee table for location 'p_loc', for example, emp_detroit
EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || p_loc;
-- Remove location from master table
DELETE FROM offices WHERE location = p_loc;
END;
/

CREATE OR REPLACE FUNCTION get_num_of_employees
(p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
-- Use concatenation to form. the table name in the SELECT statement
v_query_str := 'SELECT COUNT(*) FROM emp_'
                 || p_loc
                 || ' WHERE job = :1';
-- Execute the query and put the result row in a variable
EXECUTE IMMEDIATE v_query_str
    INTO v_num_of_employees
    USING p_job;
RETURN v_num_of_employees;
END;
/

CREATE OR REPLACE PROCEDURE list_employees
(p_loc VARCHAR2, p_job VARCHAR2)
IS
TYPE cur_typ IS REF CURSOR;
-- Define a cursor variable
v_emp_cursor cur_typ;
v_query_str   VARCHAR2(1000);
v_emp_name    VARCHAR2(20);
v_emp_num     NUMBER;
BEGIN
-- Use concatenation to form. the SELECT statement
v_query_str := 'SELECT ename, empno FROM emp_'
                 || p_loc
                 || ' WHERE job = :g_job_title';
-- Open a cursor variable for the query
OPEN v_emp_cursor FOR v_query_str USING p_job;
    -- Loop through each row to find employees who perform. the specified job
    LOOP
      -- Fetch the employee name and ID into variables
      FETCH v_emp_cursor INTO v_emp_name, v_emp_num;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      -- Process row here
    END LOOP;
CLOSE v_emp_cursor;
END;

ITPub博客

Coding Dynamic SQL Demo(动态SQL编写例子)

最新文章