Oracle PL/SQL/Stored Procedure Function/Utility Procedure
Содержание
Assert procedure
<source lang="sql">
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> --
</source>
Copy tables
<source lang="sql">
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>
</source>
Count credits
<source lang="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> 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>
</source>
Create a stored procedure to measure a table usage
<source lang="sql">
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> --
</source>
emp table lookup
<source lang="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> 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>
</source>
File dump procedure
<source lang="sql">
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> --
</source>
Procedure does not count space
<source lang="sql">
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> --
</source>
Use stored procedure to log message
<source lang="sql">
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>
</source>
Use stored procedure to output table content
<source lang="sql">
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>
</source>
