Oracle PL/SQL Tutorial/Function Procedure Packages/Procedure — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:11, 26 мая 2010

A procedure block.

PROCEDURE swapn(num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
    temp_num NUMBER;
BEGIN
    temp_num := num_one;
    num_one := num_two;
    num_two := temp_num ;
END;
/


Calling a Procedure

You call a procedure using the CALL statement.



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> CREATE OR REPLACE PROCEDURE update_employee_salary(
  2    p_factor IN NUMBER
  3  ) AS
  4    v_employee_count INTEGER;
  5  BEGIN
  6      UPDATE employee
  7      SET salary = salary * p_factor;
  8      COMMIT;
  9  EXCEPTION
 10    WHEN OTHERS THEN
 11      ROLLBACK;
 12  END update_employee_salary;
 13  /
Procedure created.
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
8 rows selected.
SQL>
SQL> CALL update_employee_salary(1.5);
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    1851.84 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    9992.67 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    9817.17 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    3517.17 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    3502.17 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    6484.17 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98   11846.67 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1849.17 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>


Create or replace a procedure

SQL> create or replace PROCEDURE swapn (num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
  2      temp_num    NUMBER;
  3  BEGIN
  4      temp_num := num_one;
  5      num_one := num_two;
  6      num_two := temp_num ;
  7  END;
  8  /
Procedure created.
SQL>


Creating stored procedures

You can store PL/SQL code inside the database.

You could store the following PL/SQL code as a standalone procedure.



SQL> set SERVEROUTPUT ON
SQL> create or replace procedure p_hello
  2  is
  3     v_string varchar2(256):="Hello, World!";
  4  begin
  5     dbms_output.put_line(v_string);
  6  end;
  7  /
Procedure created.

SQL>


Decrease salary with user procedure

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(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>
SQL> select * from emp;
Enter...
     2 Jack       Tester         6 20-02-1981   1600    300     30
     3 Wil        Tester         6 22-02-1981   1250    500     30
     4 Jane       Designer       9 02-04-1981   2975  [N/A]     20
     5 Mary       Tester         6 28-09-1981   1250   1400     30
     7 Chris      Designer       9 09-06-1981   2450  [N/A]     10
     8 Smart      Helper         4 09-12-1982   3000  [N/A]     20
     9 Peter      Manager    [N/A] 17-11-1981   5000  [N/A]     10
    10 Take       Tester         6 08-09-1981   1500      0     30
    13 Fake       Helper         4 03-12-1981   3000  [N/A]     20
9 rows selected.
SQL> create or replace
  2   procedure UPDATE_EMP(p_empno number, p_decrease number) is
  3   begin
  4   update EMP
  5   set SAL = SAL / p_decrease
  6   where empno = p_empno;
  7  end;
  8  /
Procedure created.
SQL> exec UPDATE_EMP(1,2);
PL/SQL procedure successfully completed.
SQL> exec UPDATE_EMP(1,0);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp;
Enter...
     2 Jack       Tester         6 20-02-1981   1600    300     30
     3 Wil        Tester         6 22-02-1981   1250    500     30
     4 Jane       Designer       9 02-04-1981   2975  [N/A]     20
     5 Mary       Tester         6 28-09-1981   1250   1400     30
     7 Chris      Designer       9 09-06-1981   2450  [N/A]     10
     8 Smart      Helper         4 09-12-1982   3000  [N/A]     20
     9 Peter      Manager    [N/A] 17-11-1981   5000  [N/A]     10
    10 Take       Tester         6 08-09-1981   1500      0     30
    13 Fake       Helper         4 03-12-1981   3000  [N/A]     20
9 rows selected.
SQL> drop table emp;
Table dropped.


Demonstration of a nested procedure block

SQL>
SQL>
SQL> SET ECHO ON
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>


Execute a procedure

SQL> set serveroutput on
SQL> CREATE or replace PROCEDURE my_first_proc IS
  2          greetings VARCHAR2(20);
  3  BEGIN
  4          greetings := "Hello World";
  5          dbms_output.put_line(greetings);
  6  END my_first_proc;
  7  /

SQL>
SQL> EXECUTE my_first_proc;
Hello World
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
  2          my_first_proc;
  3  END;
  4  /
Hello World
PL/SQL procedure successfully completed.
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>


Insert debug line to the stored procedure with DBMS_OUTPUT.PUT_LINE

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>


Listing Stored Procedure Information

Oracle provides several data dictionary views that provide information about procedures that are currently stored in your schema:

  1. all_errors: A list of current errors on all objects accessible to the user
  2. all_source: Text source of all stored objects accessible to the user
  3. user_objects:A list of all the objects the current user has access to
  4. dba_errors:Current errors on all stored objects in the database
  5. dba_object_size:All PL/SQL objects in the database
  6. dba_source:Text source of all stored objects in the database
  7. user_errors:Current errors on all a user"s stored objects
  8. user_source:Text source of all stored objects belonging to the user
  9. user_object_size:User"s PL/SQL objects



SELECT object_name, object_type
from user_objects
WHERE status = "INVALID";


Pass ROWTYPE to a procedure

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 update_emp (emp_rec employee%ROWTYPE) IS
  2  BEGIN
  3       UPDATE employee
  4       SET    start_date = emp_rec.start_date + 100
  5       WHERE id = emp_rec.id;
  6  END update_emp;
  7  /
Procedure created.
SQL>
SQL>
SQL> DECLARE
  2       a employee%ROWTYPE;
  3  BEGIN
  4       a.id := "01";
  5       update_emp(a);
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employee;

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin               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> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>


Procedures

You can create a procedure that contains a group of SQL and PL/SQL statements.

Procedures allow you to centralize your business logic in the database.

Procedures may be used by any program that accesses the database.

You create a procedure using the CREATE PROCEDURE statement.

The simplified syntax for the CREATE PROCEDURE statement is as follows:



CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  procedure_body
END procedure_name;


Storing PL/SQL procedure in the Database

create [or replace]
procedure procedure name (parameters)
is
     ...
    begin
        ...
    end;


Wrapping a task into a procedure

SQL>
SQL>
SQL>
SQL> declare
  2      v VARCHAR2(50):= "I just printed my <in> line!";
  3      procedure p_print(i_string in VARCHAR2, i_replace in VARCHAR2 := "new") is
  4      begin
  5          DBMS_OUTPUT.put_line(replace(i_string,"<in>", i_replace));
  6      end;
  7  begin
  8      p_print (v,"first");
  9      p_print (v,"second");
 10      p_print (v);
 11  end;
 12  /
I just printed my first line!
I just printed my second line!
I just printed my new line!
PL/SQL procedure successfully completed.
SQL>