Oracle PL/SQL/Stored Procedure Function/Insert
Содержание
- 1 Adjust salary with pl/sql
- 2 Insert data into a table in a stored procedure
- 3 Roll back data inserted in a procedure
- 4 This procedure will insert a new book into the book table.
- 5 Use a stored procedure to insert data to a table
- 6 Use stored procedure to insert value to a table and use select statement to check the result
- 7 Use two insert statements in a procedure
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>