Oracle PL/SQL/Stored Procedure Function/Insert

Adjust salary with pl/sql

   
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 TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create table empLog (
  2   ENAME VARCHAR2(20),
  3   HIREDATE DATE,
  4   SAL NUMBER(7,2),
  5   DNAME VARCHAR2(20),
  6   MIN_SAL VARCHAR2(1) );
Table created.
SQL>
SQL>
SQL> create or replace procedure report_sal_adjustment is
  2       avgSalary emp.sal%type;
  3       minSalary emp.sal%type;
  4       deptName dept.dname%type;
  5       cursor empList is select empno, ename, deptno, sal, hiredate from emp;
  6  begin
  7       for empRec in empList loop
  8           select avg(sal) into avgSalary from emp where deptno = empRec.deptno;
  9           if empRec.sal - avgSalary > 0 then
 10               select dept.dname, min(emp.sal) into deptName, minSalary from dept, emp where dept.deptno = empRec.deptno and emp.deptno = dept.deptno group by dname;
 11               if minSalary = empRec.sal then
 12                   insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, "Y");
 13               else
 14                   insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, "Y");
 15               end if;
 16           end if;
 17      end loop;
 18  end;
 19  /
Procedure created.
SQL>
SQL> exec report_sal_adjustment
PL/SQL procedure successfully completed.
SQL> /
Procedure created.
SQL>
SQL> select * from empLog;
ENAME                HIREDATE         SAL DNAME                M
-------------------- --------- ---------- -------------------- -
Black                01-MAY-81       2850 SALES                Y
SQL>
SQL> drop table empLog;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.

Insert data into a table in a stored procedure

  
SQL>
SQL>
SQL>  create table t(
  2      n number
  3    )
  4    /
Table created.
SQL>
SQL>  create or replace procedure insert_into_t( p_parm in number ) is
  2    begin
  3      insert into t values ( p_parm );
  4    end insert_into_t;
  5    /
Procedure created.
SQL>
SQL> EXEC insert_into_t(12);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
         N
----------
        12
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>

Roll back data inserted in a procedure

  
SQL>
SQL> create table MyTable(n number );
Table created.
SQL>
SQL>
SQL> create table myLogTable(
  2    username varchar2(30),
  3    date_time timestamp,
  4    message varchar2(4000) );
Table created.
SQL>
SQL>
SQL> create or replace
  2  procedure log_message( p_message varchar2 ) as
  3  pragma autonomous_transaction;
  4    begin
  5    insert into myLogTable( username, date_time, message )
  6    values ( user, current_date, p_message );
  7    commit;
  8  end log_message;
  9  /
Procedure created.
SQL>
SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> begin
  2   log_message( "About to insert into MyTable" );
  3   insert into MyTable( n )
  4   values( 12345 );
  5   log_message( "rolling back insert into MyTable" );
  6   rollback;
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable
  2
SQL> select * from myLogTable
  2
SQL> drop table myLogTable;
Table dropped.
SQL> drop table MyTable;
Table dropped.
SQL>

This procedure will insert a new book into the book table.

   
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_Category => "Database",
  4               p_Title => "A Book",
  5               p_NumPages => 500,
  6               p_Price => 34.99,
  7               p_Copyright => 2004,
  8               p_emp1 => 1);
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.
SQL>

Use a stored procedure to insert data to a table

  
SQL>
SQL>
SQL>  create table t(
  2      n number
  3    )
  4    /
Table created.
SQL>
SQL>  create or replace
  2    procedure insert_into_t( p_parm1 in number,p_parm2 in number ) is
  3    begin
  4      insert into t values ( p_parm1 );
  5      insert into t values ( p_parm2 );
  6    end insert_into_t;
  7    /
Procedure created.
SQL>
SQL>
SQL>  select * from t;
no rows selected
SQL>
SQL>  exec insert_into_t( p_parm1 => 101, p_parm2 => 102 );
PL/SQL procedure successfully completed.
SQL>
SQL>  select * from t;
         N
----------
       101
       102
SQL>
SQL> drop table t;
Table dropped.

Use stored procedure to insert value to a table and use select statement to check the result

  
SQL>
SQL>  create table t(
  2      n number
  3    )
  4    /
Table created.
SQL>
SQL>  create or replace
  2    procedure insert_into_t( p_parm in number ) is
  3    begin
  4      insert into t values ( p_parm );
  5    end insert_into_t;
  6    /
Procedure created.
SQL>
SQL>
SQL>  select * from t;
no rows selected
SQL>
SQL>  exec insert_into_t( p_parm => 100 );
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
         N
----------
       100
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
SQL>

Use two insert statements in a procedure

  
SQL>  create table t(
  2      n number
  3    )
  4    /
Table created.
SQL>
SQL>  create or replace
  2    procedure insert_into_t(
  3      p_parm1 in number,
  4      p_parm2 in number ) is
  5    begin
  6      insert into t values ( p_parm1 );
  7      insert into t values ( p_parm2 );
  8    end insert_into_t;
  9    /
Procedure created.
SQL>
SQL> begin
  2     insert_into_t(1,2);
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
         N
----------
         1
         2
SQL>
SQL> drop table t;
Table dropped.
SQL>