10 - SQL*Pl u s Pl / SQL - SQL*P lus 10-1
1 0.1 PL/ SQL SQL*Pl u s. SQL*P lus 10-2
1 0.2 S QL* Pl u s PL/ S QL SQL*Pl u s, Pl / SQL. - PL/ SQL (i npu t ), (s t or e ), (r un). - PL/ SQL s cr i pt,,. - PL/ SQL b i nd va r i ab l e s. - PL/ SQL subp r ogr am. SQL*P lus 10-3
1 0.3 An o ny mo u s Bl o c k PL/ SQL DECLARE, BEGI N. SQL*Pl u s p r omp t (SQL> ) SOL*Pl u s. - SQL bu f f e r ( ) - I NPUT mode. - s emi col on s (SOL ). SOL*Pl u s PL/ SQL, (b l ock l ab l e ). PL/ SQL. p e r i od (.). SOL bu f f e r. PL/ SQL Sc r i p t f i l e. SQL> SAVE f i l en ame Sc r i p t f i l e SQL*Pl u s l i n e e d i t or. SQL> SAVE f i l en ame REPLACE SQL*P lus 10-4
1 0.4 An o ny mo u s Bl o c k PL/ SQL, Pe r i od (.) s l a s h (/ ), SQL bu f f e r,. SQL bu f f e r. SQL> RUN SQL> / SQL*Pl u s p r omt e. SQL*P lus 10-5
1 0.5 S c r i p t e d i t e r SQL*Pl u s s t a t emen t s, PL/ SQL b l oc k s, s ubp r ogr ams, (and/ or ) p a c ka ge s s c r i p t PL/ SQL SQL*Pl u s. CLEAR BREAKS CLEAR COLUMNS COLUMN ENAME HEADI NG Name TTI TLE 'CLERI CAL STAFF' DECLARE av g_ s a l NUMBER(7,2); BEGI N SELECT AVG(s a l ) I NTO av g_ s a l FROM emp ; I F av g_ s a l < 1500 THEN UPDATE emp SET s a l = s a l * 1.05 WHERE j ob = 'CLERK' ; END I F; END; / SELECT ENAME, SAL FROM EMP WHERE J OB = 'CLERK' ; - CLEAR. - COLUMN ENAME he adi ng Name. - TTI TLE t i t l e. - SQL*Pl u s s emi col on (;). PL/ SQL Sl a sh (/ ). SQL*P lus 10-6
1 0.6 S c r i p t Sc r i p t SQL> START f i l en ame SQL> @ f i l en ame (PL/ S QL S QL *Pl us S u b s t i t u t e Va r i a b l e ) - SQL*Pl u s PL/ SQL, SQL*Pl u s. - SQL*Pl u s n um Pr ompt. SQL> BEGI N 2 FOR i I N 1..&num LOOP...... 8 END; 9 / En t e r v a l u e f or mun : SQL*P lus 10-7
1 0.7 St ored Subprogram, Package, Tr i gger PL/ SQL s ubp r ogr ams, p a c ka ge s, t r i gge r s, da t ab a s e, SQL. - CREATE FUNCTI ON - CREATE PACKAGE - CREATE PACKAGE BODY - CREATE PROCEDURE - CREATE TRI GGER, SQL*Pl us SQL bu f f er, I NPUT mode. PL/ SQL pr ocedur e, s l ash (/ ) dat abas e. SQL> CREATE PROCEDURE c r e a t e_ dep t (n ew_ name CHAR, 2 n ew_ l oc CHAR) AS 3 BEGI N 4 I NSERT I NTO dep t 5 VALUES (dep t n o_ s e q.nextval, n ew_ name, n ew_ l oc ); 6 END c r e a t e_ dep t ; 7 / Pr oc e du r e c r e a t e d SQL*Pl us Subp r ogr am, p ackage, t r i gger, SQL*Pl us SHOW ERRORS. SQL> SHOW ERRORS SQL*P lus 10-8
1 0.8.1 Bi n d Va r i a b l e s - (Bi n d v a r i a b l e s?) - SQL*Pl u s v ar i ab l e. - PL/ SQL p r ogr ams. PL/ SQL pr ogr am Var i ab l e. - SQL*Pl u s PL/ SQL. - SQL*Pl u s. (Bi n d v a r i a b l e ) SQL*Pl us command VARI ABLE. NUMBER t ype va i r ab l e. VARI ABLE r e t u r n_ c ode NUMBER ( ) PL/ SQL p r ogr am v a r i ab l e b i nd v a r i ab l e,. (PL/ S QL ) b i nd v a r i ab l e c o l on (:). :r e t u r n_ c ode := 0; I F c r e d i t _ ch e c k_ ok (a c c t _ n o) THEN :r e t u r n_ c ode := 1; END I F; (S QL *Pl us ) PRI NT. SQL> PRI NT r e t u r en_ c ode RETURN_ CODE - - - - - - - - - - - - - 1 SQL*P lus 10-9
1 0.8.2 Bi n d Va r i a b l e s - ( ) Sc r i p t REFCURSOR t yp e b i nd v a r i ab l e.(sql*pl us dat at ype REFCURSOR cur s or var i ab l e ) SQL*Pl us command SET AUTOPRI NT ON. CREATE PACKAGE emp_ da t e AS TYPE Emp Re ctyp I S RECORD ( emp_ i d NUMBER(4) emp_ nma e CHAR(10) j ob_ t i t l e CHAR(9) dep t _ name CHAR(14) d ep t _ l oc CHAR(13)); TYPE Emp Cu r Typ I S REF CURSOR RETURN Emp Re ctyp ; PROCEDURE ge t _ s t a f f (dep t _ n o I N NUMBER, emp_ cv I N OUT Emp Cu r Typ ); END; / CREATE PACKAGE BODY emp_ da t e AS PROCEDURE ge t _ s t a f f (dep t _ n o I N NUMBER, emp_ cv I N OUT) Emp Cu r Typ ) I S BEGI N OPEN emp_ cv FOR SELECT empn o, ename, j ob, dname, l oc FROM emp, dep t WHERE emp.dep t n o = dep t _ n o AND emp.dep t n o = dep t.dep t n o ORDER BY empn o ; END; END; / COLUMN EMPNO HEADI NG Numb e r COLUMN ENAME HEADI NG Name COLUMN J OB HEADI NG J obti t l e COLUMN DNAME HEADI NG Dep a r t men t COLUMN LOC HEADI NG Loc a t i on SET AUTOPRI NT ON VARI ABLE cv RECURSOR EXECUTE emp_ da t e.ge t _ s t a f f (20, :cv ) SQL*Plus PL/ SQL 10-10
1 0.9 S t o r e d s u b p r o g r a ms SQL*Pl u s l oc a l r emot e da t ab a s e (s t anda l on e p a c ka ge ) s ubp r ogr ams. l oc a l. s t anda l on e p r oc e du r e cr e a t e_ dep t SQL> EXECUTE c r e a t e_ dep t ('ADVERTI SI NG', 'NEW YOUK') PL/ SQL. SQL> BEGI N c r e a t e_ dep t ('ADVERTI SI NG', 'NEW YOUK'); END; da t ab a s e l i nk n e wy or k da t ab a s e r a i s e_ s a l a r y p r oc e du r e s SQL> EXECUTE r a i s e_ s a l a r y @n ewy or k (7499, 1500) - dat aba s e s ynonyms. SQL*Plus PL/ SQL 10-11
1 0.1 0 Ou t p u t, PL/ SQL I / O. DBMS_ OUPUT ( 7 ) p a c ka ge PL/ SQL s ubp r ogr ams Ou t pu t. debu g. (p u t _ l i n e Pr o c e d ur e & S E T S ER VEROUTP UT ON) p u t _ l i n e Pr oc e du r e i n f or ma t i on ou t pu t. SQL*Pl u s. c ommand SET SERVEROUTPUT ON St or e d p r oc e du r e CREATE PROCEDURE c a l c_ p a y r ol l (p a y r ol l I N OUT REAL) AS CURSOR c1 I S SELECT s a l,c omm FROM emp ; BEGI N p a y r ol l := 0; FOR c l r e c I N C1 l oop C1REC.COMM := NVL(c l r e c.c omm, 0); p a y r ol l := p a y r ol l + c1r e c.s a l + c1r e c.c omm; END LOOP; / * Debu g. */ dbms _ ou t pu t.pu t _ l i n e ('p a y r ol l : ' TO_ CHAR(p a y r ol l )); END c a l c_ p a y r ol l ; p r oc edu r e p ay r ol l. SQL> SET SERVEROUTPUT ON SQL> VARI ABLE num NUMBER SQL> EXECUTE c a l c_ p ay r o l l (:num) SQL*Plus PL/ SQL 10-12