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
<source lang="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.
</source>
Insert data into a table in a stored procedure
<source lang="sql">
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>
</source>
Roll back data inserted in a procedure
<source lang="sql">
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>
</source>
This procedure will insert a new book into the book table.
<source lang="sql">
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>
</source>
Use a stored procedure to insert data to a table
<source lang="sql">
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.
</source>
Use stored procedure to insert value to a table and use select statement to check the result
<source lang="sql">
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>
</source>
Use two insert statements in a procedure
<source lang="sql">
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>
</source>
