Oracle PL/SQL/Stored Procedure Function/Procedure Definition
Содержание
- 1 A forward declaration.
- 2 A local subprogram within a stored procedure
- 3 AUTHID clause in a CREATE PROCEDURE statement indicates that this procedure is being created with user"s or invoker"s rights
- 4 Creat an empty procedure
- 5 Create a stored procedure and how to call it.
- 6 Create a stored procedure with authid
- 7 Create procedure with authid
- 8 Define and call procedure
- 9 Define procedure to insert data
- 10 exception throwed out of the procedure
- 11 Forward Referencing
- 12 Increase gift price
- 13 Inner procedure
- 14 Mark procedure with authid current_user
- 15 Mutually exclusive local subprograms.
- 16 Only manager can change the password
- 17 Overloaded local procedures: number and varchar2
- 18 Reference package variable in a procedure
- 19 replace a procedure
- 20 Save calculation result to a table in procedure
- 21 Using all the default values
A forward declaration.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_TempVal BINARY_INTEGER := 5;
3
4 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);
5
6 PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE("A(" || p_Counter || ")");
9 IF p_Counter > 0 THEN
10 B(p_Counter);
11 p_Counter := p_Counter - 1;
12 END IF;
13 END A;
14
15 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
16 BEGIN
17 DBMS_OUTPUT.PUT_LINE("B(" || p_Counter || ")");
18 p_Counter := p_Counter - 1;
19 A(p_Counter);
20 END B;
21 BEGIN
22 B(v_TempVal);
23 END;
24 /
B(5)
A(4)
B(4)
A(3)
B(3)
A(2)
B(2)
A(1)
B(1)
A(0)
PL/SQL procedure successfully completed.
SQL>
SQL>
A local subprogram within a stored procedure
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE StoredProc AS
2 CURSOR c_Someemp IS SELECT fname, lname FROM emp WHERE lname > "L" ORDER BY lname;
3
4 v_FormattedName VARCHAR2(50);
5
6 FUNCTION FormatName(p_FirstName IN VARCHAR2, p_LastName IN VARCHAR2)
7 RETURN VARCHAR2 IS
8 BEGIN
9 RETURN p_FirstName || " " || p_LastName;
10 END FormatName;
11
12 BEGIN
13 FOR v_empRecord IN c_Someemp LOOP
14 v_FormattedName := FormatName(v_empRecord.fname,v_empRecord.lname);
15 DBMS_OUTPUT.PUT_LINE(v_FormattedName);
16 END LOOP;
17 END StoredProc;
18 /
Procedure created.
SQL>
SQL> BEGIN
2 StoredProc;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.
AUTHID clause in a CREATE PROCEDURE statement indicates that this procedure is being created with user"s or invoker"s rights
CREATE OR REPLACE PROCEDURE delete_emp (p_emp_id number)
AUTHID current_user IS
BEGIN
DELETE FROM emp WHERE emp_id = p_emp_id;
COMMIT;
END;
--
Creat an empty procedure
SQL>
SQL>
SQL> create or replace procedure my_proc as
2 begin
3 null;
4 end my_proc;
5 /
Procedure created.
SQL>
SQL>
Create a stored procedure and how to call it.
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewStudent (
2 p_FirstName lecturer.first_name%TYPE,
3 p_LastName lecturer.last_name%TYPE,
4 p_Major lecturer.major%TYPE) AS
5 BEGIN
6 INSERT INTO lecturer (ID, first_name, last_name,
7 major, current_credits)
8 VALUES (1, p_FirstName, p_LastName,
9 p_Major, 0);
10 END AddNewStudent;
11 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL> BEGIN
2 AddNewStudent("Z", "Z", "Computer Science");
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
MAJOR CURRENT_CREDITS
------------------------------ ---------------
10001 Scott Lawson
Computer Science 11
10002 Mar Wells
History 4
10003 Jone Bliss
Computer Science 8
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
MAJOR CURRENT_CREDITS
------------------------------ ---------------
10004 Man Kyte
Economics 8
10005 Pat Poll
History 4
10006 Tim Viper
History 4
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
MAJOR CURRENT_CREDITS
------------------------------ ---------------
10007 Barbara Blues
Economics 7
10008 David Large
Music 4
10009 Chris Elegant
Nutrition 8
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
MAJOR CURRENT_CREDITS
------------------------------ ---------------
10010 Rose Bond
Music 7
10011 Rita Johnson
Nutrition 8
10012 Sharon Clear
Computer Science 3
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
MAJOR CURRENT_CREDITS
------------------------------ ---------------
1 Z Z
Computer Science 0
13 rows selected.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Create a stored procedure with authid
SQL>
SQL> create table numbers(
2 n number,
3 username varchar2(30) )
4 /
Table created.
SQL>
SQL>
SQL> create or replace
2 procedure insert_numbers( p_num number ) authid current_user as
3 begin
4 insert into numbers values ( p_num, user );
5 end insert_numbers;
6 /
Procedure created.
SQL>
SQL> EXEC insert_numbers(1);
PL/SQL procedure successfully completed.
SQL>
SQL> drop table numbers;
Table dropped.
SQL>
SQL>
Create procedure with authid
SQL>
SQL> create or replace procedure show_objects
2 authid current_user as
3 begin
4 for c1 in (select object_name, object_type from user_objects where rownum < 50
5 order by object_name ) loop
6 dbms_output.put_line("Name: " || c1.object_name || " Type: " || c1.object_type );
7 end loop;
8 end;
9 /
Procedure created.
SQL> set serveroutput on
SQL> exec show_objects
Name: BOOTSTRAP$ Type: TABLE
Name: CCOL$ Type: TABLE
Name: CDEF$ Type: TABLE
Name: CLU$ Type: TABLE
Name: COL$ Type: TABLE
Name: CON$ Type: TABLE
Name: C_COBJ# Type: CLUSTER
Name: C_FILE#_BLOCK# Type: CLUSTER
Name: C_OBJ# Type: CLUSTER
Name: C_TS# Type: CLUSTER
Name: C_USER# Type: CLUSTER
Name: FET$ Type: TABLE
Name: FILE$ Type: TABLE
Name: ICOL$ Type: TABLE
Name: IND$ Type: TABLE
Name: I_CCOL1 Type: INDEX
Name: I_CDEF1 Type: INDEX
Name: I_CDEF2 Type: INDEX
Name: I_CDEF4 Type: INDEX
Name: I_COBJ# Type: INDEX
Name: I_COL2 Type: INDEX
Name: I_COL3 Type: INDEX
Name: I_CON1 Type: INDEX
Name: I_CON2 Type: INDEX
Name: I_FILE#_BLOCK# Type: INDEX
Name: I_FILE1 Type: INDEX
Name: I_FILE2 Type: INDEX
Name: I_IND1 Type: INDEX
Name: I_OBJ# Type: INDEX
Name: I_OBJ1 Type: INDEX
Name: I_OBJ2 Type: INDEX
Name: I_OBJ3 Type: INDEX
Name: I_PROXY_DATA$ Type: INDEX
Name: I_PROXY_ROLE_DATA$_1 Type: INDEX
Name: I_TAB1 Type: INDEX
Name: I_TS# Type: INDEX
Name: I_TS1 Type: INDEX
Name: I_UNDO1 Type: INDEX
Name: I_UNDO2 Type: INDEX
Name: I_USER1 Type: INDEX
Name: OBJ$ Type: TABLE
Name: PROXY_DATA$ Type: TABLE
Name: PROXY_ROLE_DATA$ Type: TABLE
Name: SEG$ Type: TABLE
Name: TAB$ Type: TABLE
Name: TS$ Type: TABLE
Name: UET$ Type: TABLE
Name: UNDO$ Type: TABLE
Name: USER$ Type: TABLE
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Define and call procedure
SQL>
SQL>
SQL> -- A procedure block.
SQL>
SQL>
SQL> -- Executing the swapn procedure.
SQL> -- Demonstration of a nested procedure block.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 first_number NUMBER;
3 second_number NUMBER;
4
5 PROCEDURE swapn (num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
6 temp_num NUMBER;
7 BEGIN
8 temp_num := num_one;
9 num_one := num_two;
10 num_two := temp_num ;
11 END;
12
13 BEGIN
14
15 first_number := 10;
16 second_number := 20;
17 DBMS_OUTPUT.PUT_LINE("First Number = " || TO_CHAR (first_number));
18 DBMS_OUTPUT.PUT_LINE("Second Number = " || TO_CHAR (second_number));
19
20 -- Swap the values
21 DBMS_OUTPUT.PUT_LINE("Swapping the two values now.");
22 swapn(first_number, second_number);
23
24 -- Display the results
25 DBMS_OUTPUT.PUT_LINE("First Number = " || to_CHAR (first_number));
26 DBMS_OUTPUT.PUT_LINE("Second Number = " || to_CHAR (second_number));
27 END;
28 /
First Number = 10
Second Number = 20
Swapping the two values now.
First Number = 20
Second Number = 10
PL/SQL procedure successfully completed.
SQL>
SQL>
Define procedure to insert data
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> create or replace
2 procedure insert_numbers( p_num number ) authid definer as
3 begin
4 insert into employee (id, first_name) values ( p_num, user );
5 end insert_numbers;
6 /
Procedure created.
SQL>
SQL> exec insert_numbers( 12 );
PL/SQL procedure successfully completed.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
12 sqle
9 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
exception throwed out of the procedure
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewStudent (
2 p_FirstName lecturer.first_name%TYPE,
3 p_LastName lecturer.last_name%TYPE,
4 p_Major lecturer.major%TYPE DEFAULT "Economics") AS
5 BEGIN
6 INSERT INTO lecturer VALUES (10001,
7 p_FirstName, p_LastName, p_Major, 0);
8 END AddNewStudent;
9 /
Procedure created.
SQL>
SQL> BEGIN
2 AddNewStudent("Simon", "Salovitz");
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (sqle.SYS_C004367) violated
ORA-06512: at "sqle.ADDNEWSTUDENT", line 6
ORA-06512: at line 2
SQL>
SQL> BEGIN
2 AddNewStudent(p_FirstName => "Veronica",
3 p_LastName => "Vassily");
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (sqle.SYS_C004367) violated
ORA-06512: at "sqle.ADDNEWSTUDENT", line 6
ORA-06512: at line 2
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
Forward Referencing
SQL>
SQL> DECLARE
2 PROCEDURE b (caller VARCHAR2); -- This is a forward referencing stub.
3 PROCEDURE a (caller VARCHAR2) IS
4 procedure_name VARCHAR2(1) := "A";
5 BEGIN
6 dbms_output.put_line("Procedure "A" called by ["||caller||"]");
7 b(procedure_name);
8 END;
9 PROCEDURE b (caller VARCHAR2) IS
10 procedure_name VARCHAR2(1) := "B";
11 BEGIN
12 dbms_output.put_line("Procedure "B" called by ["||caller||"]");
13 END;
14 BEGIN
15 a("Main");
16 END;
17 /
Procedure "A" called by [Main]
Procedure "B" called by [A]
PL/SQL procedure successfully completed.
SQL>
Increase gift price
SQL>
SQL>
SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY,
3 gift_type_id INTEGER ,
4 name VARCHAR2(30) NOT NULL,
5 description VARCHAR2(50),
6 price NUMBER(5, 2)
7 );
Table created.
SQL>
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, "Flower", "Birthday", 19.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, "Computer", "New Year", 30.00);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, "iPod", "Birthday", 25.99);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, "iPhone", "New Year", 13.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, "Book", "Birthday", 49.99);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE updatePrice(p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER) AS
2 v_gift_count INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO v_gift_count FROM gifts WHERE gift_id = p_gift_id;
5
6 IF v_gift_count = 1 THEN
7 UPDATE gifts SET price = price * p_factor WHERE gift_id = p_gift_id;
8 COMMIT;
9 END IF;
10 EXCEPTION
11 WHEN OTHERS THEN
12 ROLLBACK;
13 END updatePrice;
14 /
Procedure created.
SQL>
SQL>
SQL> drop table gifts;
Table dropped.
Inner procedure
SQL>
SQL>
SQL> DECLARE
2 lv_count_num PLS_INTEGER := 0;
3 PROCEDURE DISPLAY_VALUE (p_value_num PLS_INTEGER) IS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("Variable Value: " || p_value_num);
6 END;
7 BEGIN
8 display_value(lv_count_num);
9 lv_count_num := lv_count_num + 2;
10 display_value(lv_count_num);
11 lv_count_num := lv_count_num + 4;
12 display_value(lv_count_num);
13 lv_count_num := lv_count_num + 8;
14 display_value(lv_count_num);
15 END;
16 /
Variable Value: 0
Variable Value: 2
Variable Value: 6
Variable Value: 14
PL/SQL procedure successfully completed.
SQL>
Mark procedure with authid current_user
SQL>
SQL> select count(*) from dba_users;
1 row selected.
SQL>
SQL> create or replace procedure GET_ROW is
2 x number;
3 begin
4 execute immediate "select 1 from dba_users where rownum = 1" into x;
5 end;
6 /
Procedure created.
SQL> create or replace procedure GET_ROW authid current_user is
2 x number;
3 begin
4 execute immediate "select 1 from dba_users where rownum = 1" into x;
5 end;
6 /
Procedure created.
SQL>
Mutually exclusive local subprograms.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_TempVal BINARY_INTEGER := 5;
3
4 PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("A(" || p_Counter || ")");
7 IF p_Counter > 0 THEN
8 B(p_Counter);
9 p_Counter := p_Counter - 1;
10 END IF;
11 END A;
12
13 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
14 BEGIN
15 DBMS_OUTPUT.PUT_LINE("B(" || p_Counter || ")");
16 p_Counter := p_Counter - 1;
17 A(p_Counter);
18 END B;
19 BEGIN
20 B(v_TempVal);
21 END;
22 /
B(p_Counter);
*
ERROR at line 8:
ORA-06550: line 8, column 7:
PLS-00313: "B" not declared in this scope
ORA-06550: line 8, column 7:
PL/SQL: Statement ignored
SQL>
SQL>
Only manager can change the password
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL> create or replace procedure reset_password(id varchar2) is
2 v_username varchar2(30);
3 begin
4 select ename into v_username from emp where empno = id and mgr = ( select empno from emp where ename = user );
5 execute immediate "alter user ""||v_username||"" "|| " identified by "||v_username;
6 exception
7 when no_data_found then
8 raise_application_error(-20000, "You are not authorised to alter emp "||id);
9 end;
10 /
Procedure created.
SQL> drop table emp;
Table dropped.
Overloaded local procedures: number and varchar2
SQL>
SQL> DECLARE
2
3 PROCEDURE LocalProc(p1 IN NUMBER) IS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("In version 1, p1 = " ||p1);
6 END LocalProc;
7
8 PROCEDURE LocalProc(p1 IN VARCHAR2) IS
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE("In version 2, p1 = " ||p1);
11 END LocalProc;
12 BEGIN
13
14 LocalProc(12345);
15
16
17 LocalProc("abcdef");
18 END;
19 /
In version 1, p1 = 12345
In version 2, p1 = abcdef
PL/SQL procedure successfully completed.
SQL>
Reference package variable in a procedure
SQL>
SQL> create or replace procedure MY_PROC(p_input varchar2) is
2 v1 varchar2(30);
3 begin
4 v1 := p_input;
5 end;
6 /
Procedure created.
SQL> create or replace package MY_PKG is
2 glob_var varchar2(40) := "asdf";
3 end;
4 /
Package created.
SQL>
SQL> exec MY_PROC(my_pkg.glob_var);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create or replace procedure MY_PROC(p_input varchar2) is
2 v1 my_pkg.glob_var%type;
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL> create or replace package MY_PKG is
2 glob_var varchar2(60) := "asdf";
3 end;
4 /
Package created.
SQL>
replace a procedure
SQL>
SQL>
SQL> create or replace procedure my_proc as
2 begin
3 dbms_output.put_line ( "Hello World" );
4 end my_proc;
5 /
Procedure created.
SQL>
SQL> EXEC my_proc();
Hello World
PL/SQL procedure successfully completed.
SQL>
SQL>
Save calculation result to a table in procedure
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CountCredits (
2 p_ID IN lecturer.ID%TYPE)
3 RETURN NUMBER AS
4
5 v_TotalCredits NUMBER; -- Total number of credits
6 v_CourseCredits NUMBER; -- Credits for one course
7 CURSOR c_RegisteredCourses IS
8 SELECT department, course
9 FROM myStudent
10 WHERE student_id = p_ID;
11 BEGIN
12 FOR v_CourseRec IN c_RegisteredCourses LOOP
13 SELECT num_credits
14 INTO v_CourseCredits
15 FROM session
16 WHERE department = v_CourseRec.department
17 AND course = v_CourseRec.course;
18
19 v_TotalCredits := v_TotalCredits + v_CourseCredits;
20 END LOOP;
21
22 RETURN v_TotalCredits;
23 END CountCredits;
24 /
Function created.
SQL>
SQL> show error
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE CreditLoop AS
2 myLecturerID lecturer.ID%TYPE;
3 v_Credits lecturer.current_credits%TYPE;
4 CURSOR c_lecturer IS
5 SELECT ID
6 FROM lecturer;
7 BEGIN
8 OPEN c_lecturer;
9 LOOP
10 FETCH c_lecturer INTO myLecturerID;
11 v_Credits := CountCredits(myLecturerID);
12 INSERT INTO MyTable (num_col, char_col)
13 VALUES (myLecturerID, "Credits = " || TO_CHAR(v_Credits));
14 EXIT WHEN c_lecturer%NOTFOUND;
15 END LOOP;
16 CLOSE c_lecturer;
17 END CreditLoop;
18 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table MyTable;
Table dropped.
SQL> drop table myStudent;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>
Using all the default values
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewBook(
2 p_ISBN IN book.ISBN%TYPE,
3 p_Category IN book.category%TYPE := "Oracle Server",
4 p_Title IN book.title%TYPE,
5 p_NumPages IN book.num_pages%TYPE,
6 p_Price IN book.price%TYPE,
7 p_Copyright IN book.copyright%TYPE DEFAULT TO_NUMBER(TO_CHAR(SYSDATE, "YYYY")),
8 p_emp1 IN book.emp1%TYPE,
9 p_emp2 IN book.emp2%TYPE := NULL,
10 p_emp3 IN book.emp3%TYPE := NULL) AS
11
12 BEGIN
13 INSERT INTO book (isbn, category, title, num_pages, price,copyright, emp1, emp2, emp3)
14 VALUES (p_ISBN, p_Category, p_Title, p_NumPages, p_Price,p_Copyright, p_emp1, p_emp2, p_emp3);
15 END AddNewBook;
16 /
Procedure created.
SQL>
SQL> BEGIN
2 AddNewBook(p_ISBN => "0000000000",
3 p_Title => "A Really Nifty Book",
4 p_NumPages => 500,
5 p_Price => 34.99,
6 p_emp1 => 1);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>