Get documents about "SQL Oracle s Database Programming Language Remember
Document Sample
PL/SQL
Oracle's Database Programming
Language
Remember:
Set serveroutput on
• With serveroutput off (default) executing procedure:
SQL> Execute Record_Sale( 'Dick Cheney', 'Cezanne', 'kindergarten', '8', 3550, :vReturn);
PL/SQL procedure successfully completed.
• With serveroutput on:
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.
• Was it successful or not?!?!?!
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
IF recCount = 0 THEN
DBMS_OUTPUT.PUT_LINE
elsif condition then ('Customer Does Not Exist
In Database --
No Action Taken');
RETURN;
END IF;
else
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;
