My Oracle note 2 (PL/SQL programming) selects the blog from okcai

-- ===================================================== II. PL/SQL programming
-- ===================================================== ====================

1. Autonomous transactions: 8i or above, without affecting the master transaction.
In the stored procedure's is/
Pragma autonomous_transaction;
Autonomous transactions prevent nested commit, so that transactions are committed or rolled back within their own transaction zone without affecting other transactions.

2. Package
Package specification (package specification), Baotou, stores information about the package content, defines the user-visible process of the package,
Functions, data types, and variables
Create or replace package tt_aa
V1 varchar2 (10 );
V2 varchar2 (10 );
V3 number;
V4 Boolean;
Procedure proc1 (X number );
Procedure proc2 (Y varchar2 );
Procedure proc3 (Z number );
Function my_add (X number, y number) return number;
End;
The package body is optional.
Create or replace package body tt_aa
Procedure proc1 (X number)
Begin
V1: = to_char (X );
End;
Procedure proc2 (Y varchar2)
Begin
V2: = y;
End;
Procedure proc3 (Z number)
Begin
V1: = z;
End;
Procedure proc4 (X number, y number) return number
Begin
Return X + Y;
End;
End;

Call
Begin
Tt_aa.proc1 (6 );
Dbms_output.put_line (to_char (tt_aa.my_add (1, 3 ));
End;

3. dynamic SQL (using dbms_ SQL)
Create or replace procedure my_execute (SQL _string in varchar2)
V_cursor number;
V_numrows interger;
Begin
V_cursor: = dbms_ SQL .open_cursor;
Dbms_ SQL .parse (v_cursor, SQL _string, dbms_ SQL .v7 );
V_numrows: mongodbms_ SQL .exe cute (v_cursor );
Dbms_ SQL .close_cursor (v_cursor );
End;

You can
SQL> exec my_execute ('select * From tab ');
SQL> exec my_execute ('insert into test value' | '(' | ''' DDD ''' | ')');
SQL> exec my_execute ('commit ');

The query method is as follows:
For example, if you want to use a cursor to query a table, but the table is monthly, the table name may change every month.
Create or replace procedure proc_test
V_curid integer;
V_result integer;
V_strsql varchar2 (255 );
V_userid okcai. userid % type;
V_username okcai. Username % type;
Begin
V_strsql: = 'select * From okcai _ '| to_char (sysdate, 'yyyymmd ');
V_curid: = dbms_ SQL .open_cursor;
Dbms_ SQL .parse (v_curid, v_strsql, dbms_ SQL .v7 );
Dbms_ SQL .define_column (v_curid, 1, v_userid );
Dbms_ SQL .define_column (v_curid, 2, v_username, 10); -- the size must be specified.
V_result: = dbms_ SQL .execute (v_curid );
Loop
If dbms_ SQL .fetch_rows (v_curid) = 0 then
Exit; -- No, exit the loop
End if;
Dbms_ SQL .column_value (v_curid, 1, v_userid );
Dbms_ SQL .column_value (v_curid, 2, v_username );
Dbms_output.put_line (v_userid );
Dbms_output.put_line (v_username );
End loop;
Dbms_ SQL .close (v_curid );
End;

4. Execute immediate
<1>. Run DDL statements in PL/SQL
Begin
Execute immediate 'set role all ';
End;
<2>. Pass values to dynamic statements (using clause)
Declare
Rochelle depnam varchar2 (20): = 'testing ';
Rochelle loc varchar2 (10): = 'Dubai ';
Begin
Execute immediate 'insert into dept values (: 1,: 2,: 3 )'
Using 50, l_depnam, l_loc;
Commit;
End;
<3>. Retrieve values from dynamic statements (into clause)
Declare
L_cnt varchar2 (20 );
Begin
Execute immediate 'select count (1) From emp'
Into l_cnt;
Dbms_output.put_line (l_cnt );
End;
<4>. Dynamic call routine. The Bind Variable Parameter used in the routine must specify the parameter type. The struct parameter is considered as the in type, and Other types must be explicitly specified.
Declare
L_routin varchar2 (100): = 'gen2161. get_rowcnt ';
L_tblnam varchar2 (20): = 'emp ';
Rochelle CNT number;
L_status varchar2 (200 );
Begin
Execute immediate 'begin' | l_routin | '(: 2,: 3,: 4); end ;'
Using in l_tblnam, out l_cnt, in out l_status;

If l_status! = 'OK' then
Dbms_output.put_line ('error ');
End if;
End;
<5>. Pass the return value to the PL/SQL record type. The % rowtype variable is also available.
Declare
Type empdtlrec is record (empno number (4 ),
Ename varchar2 (20 ),
Deptno number (2 ));
Empdtl empdtlrec;
Begin
Execute immediate 'select empno, ename, deptno' |
'From EMP where empno = 808080'
Into empdtl;
End;
<6>. Pass and retrieve the value. Into clause before the using clause
Declare
Rochelle dept pls_integer: = 20;
Rochelle Nam varchar2 (20 );
Rochelle loc varchar2 (20 );
Begin
Execute immediate 'select dname, LOC from Dept where deptno =: 1'
Into l_nam, l_loc
Using l_dept;
End;
<7>. multi-row query option. This option is used to fill the temporary table with the insert statement, use the temporary table for further processing, or use ref cursors to correct this defect.
Declare
MAID: = 2000;
Begin
Execute immediate 'insert into temp (empno, ename) '|
'Select empno, ename from emp' |
'Where SAL>: 1'
Using l_sal;
Commit;
End;
<8>. Complete the update returning function.
Update can use returning to return the modified value. For example:
Update employees
Set job_id = 'sa _ man ', salary = salary + 1000, department_id = 140
Where last_name = 'Jones'
Returning salary * 0.25, last_name, department_id
Into: bnd1,: bnd2,: bnd3;
When execute immediate is used, you can use
Declare
L_sal pls_integer;
Begin
Execute immediate 'Update employees set salary = salary + 1000 where last_name = ''okcai ''returning into: 1'
Returning into v_ SQL;
Commit;
End;
 

5. Use ref cursor to complete the Dynamic Cursor Function
Declare
Type CT is ref cursor;
Cc ct;
V_policy acc_woff_policy % rowtype;
Begin
Open CC for 'select * From acc_woff_policy ';
Loop
Fetch CC into v_notify;
Exit when CC % notfound;
Dbms_output.put_line (v_policy.done_code );
End loop;
Close cc;
End;

6. recompile
Process of failure
SQL> exec dbms_utility.compile_schema (schema );
For example:
SQL> exec dbms_utility.compile_schema (Scott );

7. The stored procedure uses the table Type
<1>. String Array
Declare
Type regiontype is table of varchar2 (3) index by binary_integer;
V_listregion regiontype;
I number (2): = 0;
Begin
V_listregion (1): = '20140901 ';
V_listregion (2): = '20140901 ';
V_listregion (3): = '20140901 ';
V_listregion (4): = '20140901 ';
V_listregion (5): = '20140901 ';
V_listregion (6): = '20140901 ';
V_listregion (7): = NULL;
I: = 1;
While I <= v_listregion.last Loop
Dbms_output.put_line (v_listregion (I ));
I: = v_listregion.next (I );
End loop;
End;

<2>. rowtype Array
Declare
Type cmusertype is table of cm_user % rowtype index by binary_integer;
V_listuser cmusertype;
I number (5): = 0;
R_user cm_user % rowtype;
Begin
 
I: = 1;
For r_user in (select * From cm_user where rownum <= 5) loop
V_listuser (I): = r_user;
I: = I + 1;
End loop;
 
I: = 1;
While I <= v_listuser.last Loop
Dbms_output.put_line (v_listuser (I). bill_id );
I: = v_listuser.next (I );
End loop;
End;

<3>. Record Array
Declare
Type reccmusertype is record (bill_id cm_user.bill_id % type, cust_name varchar2 (25 ));
Type cmusertype is table of reccmusertype index by binary_integer;
V_listuser cmusertype;
I number (5): = 0;
R_user cm_user % rowtype;
Begin
 
I: = 1;
For r_user in (select * From cm_user where rownum <= 5) loop
V_listuser (I). bill_id: = r_user.bill_id;
V_listuser (I). cust_name: = 'customer' | I;
I: = I + 1;
End loop;
 
I: = 1;
While I <= v_listuser.last Loop
Dbms_output.put_line (v_listuser (I). bill_id );
Dbms_output.put_line (v_listuser (I). cust_name );
I: = v_listuser.next (I );
End loop;
End;

8. storage functions and processes

View the status of functions and processes
SQL> select object_name, status from user_objects where object_type = 'function ';
SQL> select object_name, status from user_objects where object_type = 'Procedure ';

View the source code of functions and processes
SQL> set long 1000
SQL> set pagesize 0
SQL> set trimspool on
SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

9. triggers

View triggers

Set long 50000;
Set heading off;
Set pagesize 2000;

Select
'Create or replace trigger "'|
Trigger_name | '"' | CHR (10) |
Decode (substr (trigger_type, 1, 1 ),
'A', 'after', 'B', 'before', 'I', 'instead of ') |
CHR (10) |
Triggering_event | CHR (10) |
'On "'| table_owner |'". "'|
Table_name | '"' | CHR (10) |
Decode (instr (trigger_type, 'ach row'), 0, null,
'For each row') | CHR (10 ),
Trigger_body
From user_triggers;

10. encryption of Oracle stored procedures
Run the following wrap command:
The following stored procedures are stored in the AA. SQL file:
Create or replace procedure testccb (I in number)
Begin
Dbms_output.put_line ('input parameter is '| to_char (I ));
End;

SQL> wrap INAME = A. SQL;
PL/SQL wrapper: Release 8.1.7.0.0-production on TUE Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993,200 0. All rights reserved.
Processing A. SQL to A. PLB
Prompt A. SQL is converted to a. PLB. This is the encrypted script. Execute a. PLB to generate the encrypted stored procedure.
Run a. PLB
SQL> @ A. PLB;

11. How to use a cursor to update data
Cursor C1 is
Select * From tablename
Where name is null for update [of column]
...
Update tablename set column =...
Where current of C1;

But if a commit is made after this method is opened, the next fetch will report a ora-01002 Error

12. How to customize exceptions
Pragma_exception_init (exception_name, error_number );
If an exception is thrown immediately
Raise_application_error (error_number, error_msg, true | false );
Number ranges from-20000 to-20999, and the maximum error message is 2048b.
Exception variable
Sqlcode error code
Sqlerrm error message

13. Execute DDL statements in PL/SQL
<1> dbms_ SQL packages earlier than 8i
<2> 8i or later versions are available
Execute immediate SQL;
Dbms_utility.exec_ddl_statement ('SQL ');

14. Write the Stored Procedure package in Java
<1>
Create or replace and compile
JAVA Source
Named "chelloworld"
Public class helloworld
{
Public static string print ()
{
Return System. Out. println ("Hello, world ");
}
};
/

<2>
Create or replace function my_helloworld return varchar2
As language Java
Name 'helloworld. Print () return java. Lang. string ';
/

<3>
Select my_helloworld from dual;