pl/sql& Stored Procedures | | Storage functions & Triggers

Plsql a bit: interactive non-process data manipulation ability strong Automatic navigation statement simple debugging simple want to rate high declaration type Way 1. Basic type 2. Reference variable 3. Record type variable basic format declare declaration begin exception End judgment Sentence if:. Then ... else End If;

Looping loop exit condition exit when ...;

End Loop;

Cursors Cursor---resltset returns multiple rows of data

Format cursor indicates OPER open fetch GO line cursor close close

Oracle Exception Handling exception

Timeout_on_resourrce Request Timeout Stored procedure store function trigger based on Plsql

Stored procedure format create (or replace) procedure as

Stored procedure storage function \ Difference: The storage function must have a return value can only accept one parameter two stored procedure optional return value optional parameter and can accept multiple parameters and return value can be a collection

You can call the stored procedure using the cursor Java

1: Create DATABASE link connectio 2. Precompiled SQL objects can create a result set of 3. Result set object (using cursor)

Sql:string sql = "{call Mypackage.queryemplist (?,?)}"; Call the stored procedure with the cursor Call.registeroutparameter (2, oracletypes.cursor); Cursor type

Data dictionary Meta Data Database framework

Triggers: Row-level triggers, statement-level triggers

Instance:

/*implement complex security checks to prohibit insertion of new employees during non-business hours non-working hours: 1. Weekend: To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ') 2. After work: To_number (To_char (Sysdate, ' Hh24 ')) not betweeen 9 and*/Create or Replace TriggerSecurityempbeforeInsert onEMPbegin   ifTo_char (Sysdate,' Day')inch('Saturday','Sunday')orTo_number (To_char (Sysdate,'hh24')) not between 9  and  -  Then         --Throw ErrorRaise_application_error (-20001,'prohibit inserting new employees during non-working hours'); End if;End;/

--the salary after the rise cannot be less than the wages before the riseCreate or Replace TriggerChecksalbeforeUpdate onEMP forEach rowbegin  --If the salary after the rise < before the salary then  if: New.sal<: Old.sal ThenRaise_application_error (-20002,'after the rise of wages can not be less than before the rise of wages. After the rise:'||: New.sal||'before the rise:'||: Old.sal); End if;End;/

pl/sql& Stored Procedures | | Storage functions & Triggers