Oracle PL/SQL/Stored Procedure Function/Utility Procedure
Содержание
Assert procedure
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE PROCEDURE ASSERT (
2 condition IN BOOLEAN,
3 message IN VARCHAR2) AS
4 BEGIN
5 IF NOT condition THEN
6 RAISE_APPLICATION_ERROR (-20000,message);
7 END IF;
8 END;
9 /
Procedure created.
SQL>
SQL>
SQL> --
Copy tables
SQL>
SQL> CREATE TABLE source (
2 key NUMBER(5),
3 value VARCHAR2(50) );
Table created.
SQL>
SQL>
SQL> CREATE TABLE destination (
2 key NUMBER(5),
3 value VARCHAR2(50) );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE CopyTables AS
2 v_Key source.key%TYPE;
3 v_Value source.value%TYPE;
4
5 CURSOR c_AllData IS
6 SELECT *
7 FROM source;
8 BEGIN
9 OPEN c_AllData;
10
11 LOOP
12 FETCH c_AllData INTO v_Key, v_Value;
13 EXIT WHEN c_AllData%NOTFOUND;
14
15 INSERT INTO destination (key, value)
16 VALUES (v_Key, TO_NUMBER(v_Value));
17 END LOOP;
18
19 CLOSE c_AllData;
20 END CopyTables;
21 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL> drop table source;
Table dropped.
SQL>
SQL> drop table destination;
Table dropped.
SQL>
SQL>
Count credits
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>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5));
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CountCredits (
2 p_ID IN lecturer.ID%TYPE)
3 RETURN NUMBER AS
4
5 v_TotalCredits NUMBER; -- Total number of credits
6 v_CourseCredits NUMBER; -- Credits for one course
7 CURSOR c_RegisteredCourses IS
8 SELECT department, course
9 FROM myStudent
10 WHERE student_id = p_ID;
11 BEGIN
12 FOR v_CourseRec IN c_RegisteredCourses LOOP
13 SELECT num_credits
14 INTO v_CourseCredits
15 FROM session
16 WHERE department = v_CourseRec.department
17 AND course = v_CourseRec.course;
18
19 v_TotalCredits := v_TotalCredits + v_CourseCredits;
20 END LOOP;
21
22 RETURN v_TotalCredits;
23 END CountCredits;
24 /
Function created.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
Create a stored procedure to measure a table usage
SQL>
SQL> set echo on
SQL>
SQL>
SQL> create table t ( x int, y char(1000) default "x" );
Table created.
SQL>
SQL> create or replace procedure measure_usage
2 as
3 l_free_blks number;
4 l_total_blocks number;
5 l_total_bytes number;
6 l_unused_blocks number;
7 l_unused_bytes number;
8 l_LastUsedExtFileId number;
9 l_LastUsedExtBlockId number;
10 l_LAST_USED_BLOCK number;
11 procedure get_data is
12 begin
13 dbms_space.free_blocks
14 ( segment_owner => USER,
15 segment_name => "T",
16 segment_type => "TABLE",
17 FREELIST_group_id => 0,
18 free_blks => l_free_blks );
19
20 dbms_space.unused_space
21 ( segment_owner => USER,
22 segment_name => "T",
23 segment_type => "TABLE",
24 total_blocks => l_total_blocks,
25 total_bytes => l_total_bytes,
26 unused_blocks => l_unused_blocks,
27 unused_bytes => l_unused_bytes,
28 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
29 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
30 LAST_USED_BLOCK => l_last_used_block ) ;
31
32
33 dbms_output.put_line( L_free_blks || " on FREELIST, " ||
34 to_number(l_total_blocks-l_unused_blocks-1 ) ||
35 " used by table" );
36 end;
37 begin
38 for i in 0 .. 10
39 loop
40 dbms_output.put( "insert " || to_char(i,"00") || " " );
41 get_data;
42 insert into t (x) values ( i );
43 commit ;
44 end loop;
45
46
47 for i in 0 .. 10
48 loop
49 dbms_output.put( "update " || to_char(i,"00") || " " );
50 get_data;
51 update t set y = null where x = i;
52 commit;
53 end loop;
54 end;
55 /
Procedure created.
SQL>
SQL> exec measure_usage
insert 00 0 on FREELIST, 0 used by table
insert 01 1 on FREELIST, 1 used by table
insert 02 1 on FREELIST, 1 used by table
insert 03 1 on FREELIST, 1 used by table
insert 04 1 on FREELIST, 1 used by table
insert 05 1 on FREELIST, 1 used by table
insert 06 1 on FREELIST, 1 used by table
insert 07 1 on FREELIST, 1 used by table
insert 08 1 on FREELIST, 2 used by table
insert 09 1 on FREELIST, 2 used by table
insert 10 1 on FREELIST, 2 used by table
update 00 1 on FREELIST, 2 used by table
update 01 1 on FREELIST, 2 used by table
update 02 1 on FREELIST, 2 used by table
update 03 1 on FREELIST, 2 used by table
update 04 2 on FREELIST, 2 used by table
update 05 2 on FREELIST, 2 used by table
update 06 2 on FREELIST, 2 used by table
update 07 2 on FREELIST, 2 used by table
update 08 2 on FREELIST, 2 used by table
update 09 2 on FREELIST, 2 used by table
update 10 2 on FREELIST, 2 used by table
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
emp table lookup
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> select * from emp;
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL>
SQL> create or replace procedure emp_lookup(
2 p_empno in number,
3 o_ename out emp.ename%type,
4 o_sal out emp.sal%type ) as
5 begin
6 select ename, sal
7 into o_ename, o_sal
8 from emp
9 where empno = p_empno;
10 exception
11 when NO_DATA_FOUND then
12 o_ename := "NULL";
13 o_sal := -1;
14 end emp_lookup;
15 /
Procedure created.
SQL>
SQL> variable name varchar2(10);
SQL> variable sal number;
SQL>
SQL> exec emp_lookup( "7782", :name, :sal );
PL/SQL procedure successfully completed.
SQL>
SQL> print name
NAME
--------------------------------
CLARK
SQL>
SQL> print sal
Salary
----------
2450
SQL>
SQL> select :name, :sal from dual;
:NAME :SAL
-------------------------------- ----------
CLARK 2450
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
File dump procedure
SQL>
SQL> create or replace procedure file_dump( p_directory in varchar2,p_filename in varchar2 )
2 as
3 type array is table of varchar2(5) index by binary_integer;
4
5 l_chars array;
6 l_bfile bfile;
7 l_buffsize number default 15;
8 l_data varchar2(30);
9 l_len number;
10 l_offset number default 1;
11 l_char char(1);
12 begin
13 l_chars(0) := "\0";
14 l_chars(13) := "\r";
15 l_chars(10) := "\n";
16 l_chars(9) := "\t";
17
18 l_bfile := bfilename( p_directory, p_filename );
19 dbms_lob.fileopen( l_bfile );
20
21 l_len := dbms_lob.getlength( l_bfile );
22 while( l_offset < l_len )
23 loop
24 l_data := utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_buffsize, l_offset ));
25
26 for i in 1 .. length(l_data)
27 loop
28 l_char := substr(l_data,i,1);
29
30 if ascii( l_char ) between 32 and 126
31 then
32 dbms_output.put( lpad(l_char,3) );
33 elsif ( l_chars.exists( ascii(l_char) ) )
34 then
35 dbms_output.put( lpad( l_chars(ascii(l_char)), 3 ) );
36 else
37 dbms_output.put( to_char(ascii(l_char),"0X") );
38 end if;
39 end loop;
40 dbms_output.new_line;
41
42 l_offset := l_offset + l_buffsize;
43 end loop;
44 dbms_lob.close( l_bfile );
45 end;
46 /
Procedure created.
SQL>
SQL>
SQL> exec file_dump( "MY_FILES", "demo17.dat" );
BEGIN file_dump( "MY_FILES", "demo17.dat" ); END;
*
ERROR at line 1:
ORA-22285: non-existent directory or file for
FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "sqle.FILE_DUMP", line 19
ORA-06512: at line 1
SQL>
SQL> --
Procedure does not count space
SQL> --
SQL>
SQL> CREATE OR REPLACE function dontcountsp(p_pass_string VARCHAR2)
2 RETURN NUMBER IS
3 v_MYCOUNTER INTEGER := 1;
4 v_COUNTNOSP NUMBER := 0;
5 BEGIN
6 WHILE v_MYCOUNTER <= LENGTH(p_PASS_STRING) LOOP
7 IF SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) != " " THEN
8 v_COUNTNOSP := v_COUNTNOSP + 1;
9 ELSE
10 NULL;
11 END IF;
12 v_MYCOUNTER := v_MYCOUNTER + 1;
13 END LOOP;
14 RETURN v_COUNTNOSP ;
15 END dontcountsp;
16 /
Function created.
SQL>
SQL> DECLARE
2 v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Total count is " || dontcountsp(v_MYTEXT));
5 END;
6 /
Total count is 11
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --
Use stored procedure to log message
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 procedure log_message( p_message varchar2 ) as
2 pragma autonomous_transaction;
3 begin
4 insert into myLogTable( username, date_time, message )
5 values ( user, current_date, p_message );
6 commit;
7 end log_message;
8 /
Procedure created.
SQL>
SQL> begin
2 log_message("hi");
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myLogTable;
USERNAME DATE_TIME
------------------------------ ---------------------------------------------------------------------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS 11-JUN-08 08.44.26.000000 PM
hi
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
SQL>
SQL>
Use stored procedure to output table content
SQL>
SQL> set serveroutput on
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> create or replace procedure show_emp( p_empno in number )
2 is
3 begin
4 for c1 in (select * from emp where empno = p_empno) loop
5
6 dbms_output.put_line("Name: " || c1.ename);
7 dbms_output.put_line("Job: " || c1.job);
8 dbms_output.put_line("Salary: " || c1.sal);
9 dbms_output.put_line("Commission: " || c1.rum);
10 end loop;
11 end show_emp;
12 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL> /
Procedure created.
SQL>
SQL>
SQL> EXEC show_emp(7934);
Name: MILLER
Job: CLERK
Salary: 1300
Commission:
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>