Oracle PL/SQL/PL SQL/Type
Содержание
- 1 Add row to table with tableName.columnName%type
- 2 Column%type parameter
- 3 Creating a procedure and call it
- 4 Declare scalars based on the datatype of a previously declared variable
- 5 Passing %TYPE and %ROWTYPE as Parameters
- 6 reference table data with tableName.columnName%type
- 7 rowtype and type
- 8 Select only one row for column type variable
Add row to table with tableName.columnName%type
SQL>
SQL> create table myTable (
2 col varchar2(10)
3 );
Table created.
SQL>
SQL> create or replace procedure ADD_ROW(p_col myTable.COL%TYPE) is
2 begin
3 insert into myTable values (p_col);
4 end;
5 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Column%type parameter
SQL>
SQL> create table myTable (
2 col varchar2(10)
3 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure ADD_NUM_ROW(p_col myTable.COL%TYPE) is
2 begin
3 insert into myTable values (p_col);
4 end;
5 /
Procedure created.
SQL>
SQL>
SQL> show errors;
No errors.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>
Creating a procedure and call it
SQL>
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewStudent (
2 p_FirstName lecturer.first_name%TYPE,
3 p_LastName lecturer.last_name%TYPE,
4 p_Major lecturer.major%TYPE) AS
5 BEGIN
6 INSERT INTO lecturer (ID, first_name, last_name,
7 current_credits, major)
8 VALUES (1, p_FirstName, p_LastName,
9 0, p_Major);
10
11
12 END AddNewStudent;
13 /
Procedure created.
SQL>
SQL> DECLARE
2 v_NewFirstName lecturer.first_name%TYPE := "Margaret";
3 v_NewLastName lecturer.last_name%TYPE := "Mason";
4 v_NewMajor lecturer.major%TYPE := "History";
5 BEGIN
6 AddNewStudent(v_NewFirstName, v_NewLastName, v_NewMajor);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
---------- -------------------- -------------------- ------------------------------ ---------------
10001 Scott Lawson Computer Science 11
10002 Mar Wells History 4
10003 Jone Bliss Computer Science 8
10004 Man Kyte Economics 8
10005 Pat Poll History 4
10006 Tim Viper History 4
10007 Barbara Blues Economics 7
10008 David Large Music 4
10009 Chris Elegant Nutrition 8
10010 Rose Bond Music 7
10011 Rita Johnson Nutrition 8
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
---------- -------------------- -------------------- ------------------------------ ---------------
10012 Sharon Clear Computer Science 3
1 Margaret Mason History 0
13 rows selected.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
Declare scalars based on the datatype of a previously declared variable
SQL>
SQL>
SQL> --%TYPE attribute
SQL>
SQL> --Declare scalars based on the datatype of a previously declared variable.
SQL>
SQL>
SQL> DECLARE
2 x NUMBER(7,2) := 100;
3 y x%TYPE;
4 BEGIN
5 y := 200;
6
7 DBMS_OUTPUT.PUT_LINE(x);
8 DBMS_OUTPUT.PUT_LINE(y);
9 END;
10
11 /
100
200
PL/SQL procedure successfully completed.
SQL>
Passing %TYPE and %ROWTYPE as Parameters
SQL>
SQL> create table myTable (
2 col varchar2(10)
3 );
Table created.
SQL>
SQL>
SQL> create or replace procedure ADD_ROW(p_col myTable.COL%TYPE) is
2 begin
3 insert into myTable values (p_col);
4 end;
5 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
reference table data with tableName.columnName%type
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>
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>
SQL> create or replace procedure report_sal_adjustment2 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(emp.sal), min(emp.sal), dept.dname into avgSalary, minSalary,deptName from dept, emp where dept.deptno = empRec.deptno and emp.deptno = dept.deptno group by dname;
9 if empRec.sal - avgSalary > 0 then
10 if minSalary = empRec.sal then
11 insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, "Y");
12 else
13 insert into empLog values ( empRec.ename, empRec.hiredate, empRec.sal, deptName, "Y");
14 end if;
15 end if;
16 end loop;
17 end;
18 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> drop table empLog;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
rowtype and type
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 l_dept departments%rowtype;
3 l_another_dept departments.department_name%type;
4 begin
5 l_dept.department_id := 1000;
6 l_dept.department_name := "Graphic Art";
7
8 insert into departments(
9 department_id, department_name)
10 values(
11 l_dept.department_id, l_dept.department_name);
12
13 l_dept.department_id := 1001;
14 l_another_dept := "Web Design/User Interface";
15
16 insert into departments(
17 department_id, department_name)
18 values(
19 l_dept.department_id, l_another_dept);
20
21 dbms_output.put_line("The departments created were " ||
22 l_dept.department_name || " and " || l_another_dept);
23 end;
24 /
The departments created were Graphic Art and Web Design/User Interface
PL/SQL procedure successfully completed.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
Select only one row for column type variable
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 l_empno emp.empno%type;
3 l_ename emp.ename%type;
4 begin
5 select empno, ename
6 into l_empno, l_ename
7 from emp
8 where rownum = 1;
9 dbms_output.put_line(l_empno||":"||l_ename);
10 end;
11 /
7369:SMITH
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>