Download
1 / 14
140 likes | 343 Views
PL/SQL. Oracle's Database Programming Language. Remember: Set serveroutput on. With serveroutput off (default) executing procedure: With serveroutput on: Was it successful or not?!?!?!. SQL> Execute Record_Sale( 'Dick Cheney', 'Cezanne', 'kindergarten', '8', 3550, :vReturn);
E N D
PL/SQL Oracle's Database Programming Language
Remember:Set serveroutput on • With serveroutput off (default) executing procedure: • With serveroutput on: • Was it successful or not?!?!?! SQL> Execute Record_Sale( 'Dick Cheney', 'Cezanne', 'kindergarten', '8', 3550, :vReturn); PL/SQL procedure successfully completed. SQL> Execute Record_Sale( 'Dick Cheney', 'Cezanne', 'kindergarten', '8', 3550, :vReturn); Entered stored procedure Looking up CustomerID Looking up ArtistID Finding Work record Finding Transaction record Testing to see if a Transaction record was found No valid Transaction record exists. Transaction not completed. PL/SQL procedure successfully completed.
PL/SQL anonymous code block declare ... <declarations> ... begin ... <procedure> ... exception (optional) ... <exception handlers> ... end; (note ;)
Hello World! declare /* note common Oracle variable name style: v prefix means local variable tx suffix means text */ v_message_tx varchar2(50) := 'Hello World'; begin /* to see this, serveroutput must be on */ dbms_output.put_line( v_message_tx ); end; sql> get HelloWorld.sql ... sql> / Hello World
Simple Stored Procedure -- in the file sp_hello.sql: create or replace procedure sp_hello is v_message_tx varchar2(50):='Hello World'; begin dbms_output.put_line(v_message_tx); end; SQL> @ sp_hello 6 / Procedure created. SQL> begin 2 sp_hello; 3 end; 4 / Hello World PL/SQL procedure successfully completed.
Function (returns a value) CREATE OR REPLACE FUNCTION f_area_nr( i_width_nr NUMBER, i_ht_nr NUMBER ) return NUMBER is -- Function header to compute area -- i:IN parameters, nr:NUMBER v_rtn_nr NUMBER; -- Declaration of local variable -- v:local variable, nr:NUMBER begin v_rtn_nr := i_width_nr * i_ht_nr; return v_rtn_nr; end; SQL> list 1 begin 2 dbms_output.put_line( f_area_nr(3,4)); 3* end; SQL> / 12 PL/SQL procedure successfully completed.
Dealing with compilation errors 1 CREATE OR REPLACE FUNCTION f_area_nr( i_width_nr NUMBER, 2 i_ht_nr NUMBER ) 3 return NUMBER is 4 declare 5 v_rtn_nr NUMBER; //n.b. this function is in error 6 begin 7 v_rtn_nr := i_width_nr * i_ht_nr; 8 return v_rtn_nr; 9* end; SQL> / Warning: Function created with compilation errors. SQL> show errors Errors for FUNCTION F_AREA_NR: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/5 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language The symbol "begin" was substituted for "DECLARE" to continue.
IN and OUT parameters in procedures create or replace procedure sp_hourMin (i_date_dt DATE, -- Input parameter o_hour_nr OUT NUMBER, -- Return parameter o_minute_nr OUT NUMBER) is -- Return parameter begin o_hour_nr := to_NUMBER(to_char(i_date_dt, 'hh24')); o_minute_nr := to_NUMBER(to_char(i_date_dt, 'mi')); end; SQL> list 1 declare 2 v_hr_nr NUMBER; 3 v_mn_nr NUMBER; 4 begin 5 sp_hourMin(sysdate, v_hr_nr, v_mn_nr); 6 dbms_output.put_line('hour: ' || v_hr_nr || ' min: ' || v_mn_nr); 7* end; SQL> / hour: 17 min: 46 PL/SQL procedure successfully completed.
What did that procedure do? SQL> SELECT TEXT FROM USER_SOURCE WHERE NAME = 'SP_HOURMIN'; TEXT -------------------------------------------------------------------------------- procedure sp_hourMin (i_date_dt DATE, o_hour_nr OUT NUMBER, o_minute_nr OUT NUMBER) is begin o_hour_nr := to_NUMBER(to_char(i_date_dt, 'hh24')); o_minute_nr := to_NUMBER(to_char(i_date_dt, 'mi')); end; 6 rows selected.
What was the name of that routine? SQL> column object_type format A12; SQL> column object_name format A12; SQL> select object_type, object_name, status 2 from user_objects 3 where object_type in ('FUNCTION', 'PROCEDURE'); OBJECT_TYPE OBJECT_NAME STATUS ------------ ------------ ------- FUNCTION DECLARE INVALID FUNCTION FINDAREA INVALID FUNCTION F_AREA_NR VALID PROCEDURE SP_HELLO VALID PROCEDURE SP_HOURMIN VALID
IF statements if condition then elsif condition then else end if; IF recCount = 0 THEN DBMS_OUTPUT.PUT_LINE ('Customer Does Not Exist In Database -- No Action Taken'); RETURN; END IF;
Loops loop . . . exit when condition . . . end loop; FOR Trans_record in TransactionCursor LOOP IF( Trans_Record.SalesPrice is null ) THEN v_TransactionFound := TRUE; DBMS_OUTPUT.PUT_LINE( 'Updating Transaction' ); UPDATE Transaction SET SalesPrice = v_Price, CustomerID = v_CustomerID, PurchaseDate = SYSDATE WHERE CURRENT OF TransactionCursor; END IF; EXIT WHEN v_TransactionFound; END LOOP;
Exceptions Typical Exception Block: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( 'No data found' ); v_Return := 'Exception: No data found'; ROLLBACK; WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE( 'Too many rows found' ); v_Return := 'Exception: Too many rows found'; ROLLBACK; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Exception occurred' ); DBMS_OUTPUT.PUT_LINE( 'Error code: ' || SQLCODE); DBMS_OUTPUT.PUT_LINE( SQLERRM ); v_Return := ( 'Exception: ' || SQLERRM ); ROLLBACK;
Cursors CURSOR TransactionCursor IS SELECT SalesPrice FROM Transaction WHERE WorkID = v_WorkID FOR UPDATE OF SalesPrice, CustomerID, PurchaseDate; . . . FOR Trans_record in TransactionCursor LOOP IF( Trans_Record.SalesPrice is null ) THEN v_TransactionFound := TRUE; DBMS_OUTPUT.PUT_LINE( 'Updating Transaction' ); UPDATE Transaction SET SalesPrice = v_Price, CustomerID = v_CustomerID, PurchaseDate = SYSDATE WHERE CURRENT OF TransactionCursor; END IF; EXIT WHEN v_TransactionFound; END LOOP;
