Oracle PL/SQL Tutorial/Function Procedure Packages/Create Procedure
Содержание
- 1 Call a trigger in procedure
- 2 Create procedure for AUTHID CURRENT_USER
- 3 Creating a procedure
- 4 Creating a Stored Procedure for table update
- 5 Exceptions in Subprograms
- 6 Forward Declarations
- 7 Re-creating a Procedure By Using OR REPLACE
- 8 Using stored functions in SQL statements, function getName
Call a trigger in procedure
CREATE TABLE authors (
2 id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50)
5 );
Table created.
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (1, "Marlene", "Theriault");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (2, "Rachel", "Carmichael");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (3, "James", "Viscusi");
1 row created.
SQL>
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> PROMPT
SQL> PROMPT ** Create an BEFORE UPDATE trigger on the AUTHORS table
** Create an BEFORE UPDATE trigger on the AUTHORS table
SQL> PROMPT
SQL>
SQL> CREATE OR REPLACE TRIGGER author_trig
2 BEFORE UPDATE OF first_name
3 ON authors
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("First Name "
7 ||:OLD.first_name
8 ||" has change to "
9 ||:NEW.first_name);
10 END;
11 /
Trigger created.
SQL>
SQL> PROMPT
SQL> PROMPT ** Create a procedure that will cause the author_trig to fire
** Create a procedure that will cause the author_trig to fire
SQL> PROMPT
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_first_name_upd (
2 i_author_id IN AUTHORS.ID%TYPE,
3 i_first_name IN AUTHORS.FIRST_NAME%TYPE)
4 IS
5 BEGIN
6
7 UPDATE authors a
8 SET a.first_name = UPPER(i_first_name)
9 WHERE a.id = i_author_id;
10
11 EXCEPTION
12 WHEN OTHERS
13 THEN
14 DBMS_OUTPUT.PUT_LINE(sqlerrm);
15 END;
16 /
Procedure created.
SQL>
SQL> SET ESCAPE OFF
SQL>
SQL>
SQL> drop table authors;
Table dropped.
SQL>
SQL>
Create procedure for AUTHID CURRENT_USER
SQL>
SQL> CREATE TABLE customer
2 (customer_id NUMBER(7),
3 customer_name VARCHAR2(50),
4 phone VARCHAR2(15),
5 address VARCHAR2(400),
6 city VARCHAR2(35),
7 state VARCHAR2(30),
8 country VARCHAR2(30),
9 zip_code VARCHAR2(10),
10 credit_rating VARCHAR2(9),
11 sales_rep_id NUMBER(7),
12 region_id NUMBER(7),
13 comments VARCHAR2(255),
14 preferred_customer VARCHAR2(1) DEFAULT "N" NOT NULL,
15 shipping_method VARCHAR2(1) DEFAULT "M" NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, "Jane", "111-1111", "7 AVE","SAO", NULL, "BRAZIL", NULL, "EXCELLENT",12, 2, "A", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (202, "Todd", "222-2222", "6 BLVD.","OSAKA", NULL, "JAPAN", NULL, "POOR", 14, 4, "B", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (203, "Sharon", "333-3333", "1 STREET", "NEW DELHI", NULL, "INDIA", NULL, "GOOD", 14, 4,"C", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (204, "Hong", "444-4444", "2 STREET","SEATTLE", "WASHINGTON", "USA", "98101", "EXCELLENT",11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (205, "Anderson","555-5555", "5 ROAD", "HONG KONG", NULL, NULL,NULL, "EXCELLENT", 15, 4, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (206, "Bob", "666-6666", "1 ROAD","CANNES", NULL, "FRANCE", NULL, "EXCELLENT", 15, 5,"D", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (207, "Cat", "777-7777", "6 STREET","LAGOS", NULL, "NIGERIA", NULL, "GOOD", NULL, 3, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (208, "Doge", "888-8888", "4 RASSE", "STUTTGART", NULL, "GERMANY", NULL, "GOOD", 15, 5,"E", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (209, "Black", "999-9999", "2 MAR","SAN PEDRO DE MACON""S", NULL, "DOMINICAN REPUBLIC",NULL, "EXCELLENT", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (210, "Red", "000-0000", "3 ARO","NOGALES", NULL, "MEXICO", NULL, "EXCELLENT", 12, 2,"Customer is difficult to reach by phone. Try mail.","N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (211, "Ted", "123-1231", "7 MOD", "PRAGUE",NULL, "CZECHOSLOVAKIA", NULL, "EXCELLENT", 15, 5, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (212, "Homas", "124-1234", "5 COR","ALEXANDRIA", NULL, "EGYPT", NULL, "EXCELLENT", 13, 3,"F", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (213, "Look", "555-6281", "4 STREET", "SAN FRANCISCO", "CA", "USA", "94117","EXCELLENT", 11, 1, "G", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (214, "Yellow", "555-7171", "4 STREET","BUFFALO", "NY", "USA", "14202", "POOR", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (215, "White", "337-3892", "6 YEK","SAINT PETERSBURG", NULL, "RUSSIA", NULL, "POOR",15, 5, "T", "N", "M");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE display_customers
2 AUTHID CURRENT_USER IS
3 CURSOR cur_cust IS
4 SELECT customer_id, customer_name
5 FROM customer;
6 BEGIN
7 FOR cur_cust_rec IN cur_cust LOOP
8 DBMS_OUTPUT.PUT_LINE("Customer Id: " || cur_cust_rec.customer_id || CHR(9) || " Customer Name: " || cur_cust_rec.customer_name);
9 END LOOP;
10 END display_customers;
11 /
Procedure created.
SQL>
SQL> drop table customer;
Table dropped.
SQL>
Creating a procedure
SQL>
SQL>
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 sequence student_sequence;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewEmployee (
2 p_FirstName employee.first_name%TYPE,
3 p_LastName employee.last_name%TYPE,
4 p_Salary employee.salary%TYPE) AS
5 BEGIN
6 INSERT INTO employee (ID, first_name, last_name,
7 salary)
8 VALUES (student_sequence.nextval, p_FirstName, p_LastName,
9 p_Salary);
10
11 COMMIT;
12 END AddNewEmployee;
13 /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL> --Calling a procedure
SQL> DECLARE
2 v_NewFirstName employee.first_name%TYPE := "Margaret";
3 v_NewLastName employee.last_name%TYPE := "Mason";
4 v_NewSalary employee.salary%TYPE := 2000;
5 BEGIN
6 -- Add Margaret Mason to the database.
7 AddNewEmployee(v_NewFirstName, v_NewLastName, v_NewSalary);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
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
1 Margaret Mason 2000
9 rows selected.
SQL>
SQL> drop sequence student_sequence;
Sequence dropped.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Creating a Stored Procedure for table update
SQL>
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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> CREATE OR REPLACE PROCEDURE emp_change_s (i_emp_id IN VARCHAR2) AS
2 BEGIN
3 UPDATE employee set City = "New" WHERE id = i_emp_id;
4 END emp_change_s;
5 /
Procedure created.
SQL>
SQL> call emp_change_s("01");
Call 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 New 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Exceptions in Subprograms
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
2 p_Raise IN BOOLEAN := TRUE,
3 p_ParameterA OUT NUMBER) AS
4 BEGIN
5 p_ParameterA := 7;
6
7 IF p_Raise THEN
8 RAISE DUP_VAL_ON_INDEX;
9 ELSE
10 RETURN;
11 END IF;
12 END RaiseError;
13 /
SP2-0804: Procedure created with compilation warnings
SQL> DECLARE
2 v_TempVar NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.put_line("Initial value");
5 RaiseError(FALSE, v_TempVar);
6
7 DBMS_OUTPUT.put_line("Value after successful call");
8
9 v_TempVar := 2;
10 DBMS_OUTPUT.put_line("Value before 2nd call");
11 RaiseError(TRUE, v_TempVar);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.put_line("Value after unsuccessful call");
15 END;
16 /
Initial value
Value after successful call
Value before 2nd call
Value after unsuccessful call
PL/SQL procedure successfully completed.
SQL>
SQL>
Forward Declarations
SQL>
SQL>
SQL> DECLARE
2 v_TempVal BINARY_INTEGER := 5;
3
4 -- Forward declaration of procedure B.
5 PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);
6
7 PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
8 BEGIN
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 p_Counter := p_Counter - 1;
18 A(p_Counter);
19 END B;
20 BEGIN
21 B(v_TempVal);
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Re-creating a Procedure By Using OR REPLACE
SQL>
SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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> CREATE OR REPLACE PROCEDURE emp_change_s (i_emp_id IN VARCHAR2) AS
2 BEGIN
3 UPDATE employee set City = "New" WHERE id = i_emp_id;
4
5 DBMS_OUTPUT.PUT_LINE ("updated ");
6 END emp_change_s;
7 /
Procedure created.
SQL>
SQL> call emp_change_s("01");
updated
Call 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 New 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Using stored functions in SQL statements, function getName
SQL>
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION getName
2 (ip_product_id NUMBER,
3 ip_company_id NUMBER)
4 RETURN VARCHAR2
5 IS
6 v_name VARCHAR2(120);
7 BEGIN
8
9 SELECT "Org Name: (Short) "||company_short_name||" (Long) "||company_long_name
10 INTO v_name
11 FROM company
12 WHERE product_id = ip_product_id
13 AND company_id = ip_company_id;
14
15 RETURN (v_name);
16 END getName;
17 /
Function created.
SQL>
SQL> SELECT getName(product_id,company_id) "Formatted Org Name"
2 FROM company
3 ORDER BY product_id,company_id;
Formatted Org Name
----------------------------------------------------------------------
Org Name: (Short) A Inc. (Long) Long Name A Inc.
Org Name: (Short) B Inc. (Long) Long Name B Inc.
Org Name: (Short) C Inc. (Long) Long Name C Inc.
Org Name: (Short) D Inc. (Long) Long Name D Inc.
Org Name: (Short) E Inc. (Long) Long Name E Inc.
Org Name: (Short) F Inc. (Long) Long Name F Inc.
6 rows selected.
SQL>
SQL> drop table company;
Table dropped.
SQL>