티스토리 뷰
다음 내용은 최신 버전이 아닐 수 있습니다. 최신 버전은 위 사이트를 참조하세요.
General | ||||||||||||||||||||||
Related Data Dictionary Objects |
|
|||||||||||||||||||||
System Privileges Related To Procedures |
|
|||||||||||||||||||||
Object Privileges | GRANT execute ON <procedure_name>; Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID. |
|||||||||||||||||||||
GRANT execute ON testproc TO uwclass; | ||||||||||||||||||||||
Stored Procedure | ||||||||||||||||||||||
No Parameters |
CREATE OR REPLACE PROCEDURE <procedure_name> IS BEGIN <code here> END <procedure_name>; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
no_param IS BEGIN dbms_output.put_line('No Params'); END no_param; / set serveroutput on exec no_param; |
||||||||||||||||||||||
Single IN Parameter |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> IN <data_type>) IS BEGIN <code here> END <procedure_name>; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
in_param (mesg VARCHAR2) IS
BEGIN dbms_output.put_line(mesg); END in_param; / set serveroutput on exec in_param('Single IN Parameter'); |
||||||||||||||||||||||
OUT Parameter |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> OUT <data_type>) IS BEGIN <code here> END <procedure_name>; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
out_param(mesg OUT VARCHAR2) IS BEGIN mesg := 'Single OUT Parameter'; END out_param; / set serveroutput on DECLARE s VARCHAR2(50); BEGIN out_param(s); dbms_output.put_line(s); END; / |
||||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
out_param (mesg OUT VARCHAR2) IS
BEGIN mesg := 'Single OUT Parameter'; END out_param; / SQL> var x VARCHAR2(30) SQL> exec out_param(:x) SQL> print x |
||||||||||||||||||||||
IN OUT Parameter |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> IN OUT <data_type>) IS BEGIN <code here> END <procedure_name>; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
inout_param (mesg IN OUT VARCHAR2) IS BEGIN mesg := mesg || ' an IN OUT Parameter'; END inout_param; / set serveroutput on DECLARE s VARCHAR2(50) := 'This procedure uses'; BEGIN inout_param(s); dbms_output.put_line(s); END; / |
||||||||||||||||||||||
Multiple Parameters |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> IN <data_type>, <parameter_name> OUT <data_type>, <parameter_name> IN OUT <data_type>) IS BEGIN <code here> END <procedure_name>; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
many_params ( mesg1 IN VARCHAR2, mesg2 OUT VARCHAR2, mesg3 IN OUT VARCHAR2) IS BEGIN mesg2 := mesg1 || 'Parameter As The OUT'; mesg3 := mesg3 || 'Returned'; END many_params; / set serveroutput on DECLARE iparm VARCHAR2(50) := 'This is the IN '; oparm VARCHAR2(50); ioparm VARCHAR2(50) := 'And This is the IN OUT '; BEGIN many_params(iparm, oparm, ioparm); dbms_output.put_line(oparm || ' ' || ioparm); END; / |
||||||||||||||||||||||
Parameter DEFAULT | ||||||||||||||||||||||
Procedure Without Default |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> <data_type> <default_value>) IS BEGIN <code> END; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
no_default(num_rows PLS_INTEGER) IS BEGIN FOR r IN (SELECT object_name FROM all_objects WHERE rownum < num_rows+1) LOOP dbms_output.put_line(r.object_name); END LOOP; END no_default; / set serveroutput on exec no_default exec no_default(5); |
||||||||||||||||||||||
Procedure With Default |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> <data_type> DEFAULT <default_value>) IS BEGIN <code> END; / |
|||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
with_default ( num_rows PLS_INTEGER DEFAULT 20) IS BEGIN FOR r IN (SELECT object_name FROM all_objects WHERE rownum < num_rows+1) LOOP dbms_output.put_line(r.object_name); END LOOP; END with_default; / set serveroutput on exec with_default exec with_default(5); |
||||||||||||||||||||||
Parameter Notations | ||||||||||||||||||||||
Positional Notation |
exec <procedure_name> (<parameter>,<parameter>); | |||||||||||||||||||||
CREATE OR REPLACE PROCEDURE
positional ( min_nr PLS_INTEGER DEFAULT 100, max_nr PLS_INTEGER DEFAULT 1000) IS BEGIN FOR r IN (SELECT table_name FROM user_tables WHERE num_rows BETWEEN min_nr AND max_nr) LOOP dbms_output.put_line(r.table_name); END LOOP; END positional; / set serveroutput on exec positional; exec positional(1); exec positional(1000, 500000); |
||||||||||||||||||||||
Named Notation |
exec <procedure_name> (<parameter_name> => <parameter>); | |||||||||||||||||||||
exec positional; exec positional(min_nr => 1); exec positional(max_nr => 500); exec positional(max_nr => 5000); exec positional(max_nr => 10000); exec positional(max_nr => 50000); exec positional(max_nr => 999999); |
||||||||||||||||||||||
Mixed Notation | exec <procedure_name> (<parameter>, <parameter_name> => <parameter>); | |||||||||||||||||||||
exec positional(10, max_nr => 1000); exec dbms_stats(USER, cascade=>TRUE); |
||||||||||||||||||||||
Parameter NOCOPY | ||||||||||||||||||||||
Note: NOCOPY is a hint ... not a directive ... and may be ignored. | ||||||||||||||||||||||
NOCOPY Performance Demo |
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameter_name> NOCOPY <data_type> <default_value>) IS BEGIN <code> END; / |
|||||||||||||||||||||
--
Note: This first demo shows an example of NOCOPY not working conn / as sysdba GRANT execute ON dbms_crypto TO uwclass; conn uwclass/uwclass CREATE OR REPLACE PROCEDURE default_out (retval OUT VARCHAR2) AS BEGIN retval := dbms_crypto.randombytes(32); END default_out; / CREATE OR REPLACE PROCEDURE nocopy_out (retval OUT NOCOPY VARCHAR2) AS BEGIN retval := dbms_crypto.randombytes(32); END nocopy_out; / CREATE OR REPLACE PROCEDURE nocopy_test (reps IN PLS_INTEGER) AS bt1 TIMESTAMP(9); et1 TIMESTAMP(9); bt2 TIMESTAMP(9); et2 TIMESTAMP(9); outval VARCHAR2(64); BEGIN bt1 := SYSTIMESTAMP; FOR i IN 1..reps LOOP default_out(outval); END LOOP; et1 := SYSTIMESTAMP; bt2 := SYSTIMESTAMP; FOR i IN 1..reps LOOP nocopy_out(outval); END LOOP; et2 := SYSTIMESTAMP; dbms_output.put_line('Default: ' || TO_CHAR(et1-bt1)); dbms_output.put_line('No Copy: ' || TO_CHAR(et2-bt2)); END nocopy_test; / -- Note: Tried this with REF CURSORS returning 40K rows with, again, -- no measurable difference. But now watch what happens when used in a -- demo received from Tom Kyte on 2/9/2007. CREATE OR REPLACE PROCEDURE p3(x IN OUT dbms_sql.varchar2s)
AS |
||||||||||||||||||||||
Note: If a subprogram exists with an unhandled exception, the value assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, the changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back." | ||||||||||||||||||||||
Error Handling With NOCOPY |
CREATE OR REPLACE PROCEDURE
raise_error ( p_Raise BOOLEAN, p_ParameterA OUT NOCOPY NUMBER) AS BEGIN p_ParameterA := 7; IF p_Raise THEN RAISE DUP_VAL_ON_INDEX; ELSE RETURN; END IF; EXCEPTION WHEN OTHERS THEN RETURN; END raise_error; / set serveroutput on DECLARE p_B BOOLEAN := TRUE; n NUMBER; BEGIN raise_error(p_B, n); dbms_output.put_line(n); END; / CREATE OR REPLACE PROCEDURE raise_error ( p_Raise BOOLEAN, p_ParameterA IN OUT NOCOPY NUMBER) AS BEGIN IF p_Raise THEN RAISE DUP_VAL_ON_INDEX; ELSE p_ParameterA := 999; END IF; EXCEPTION WHEN OTHERS THEN NULL; END raise_error; / DECLARE p_B BOOLEAN := FALSE; n NUMBER := 100; BEGIN raise_error(p_B, n); dbms_output.put_line(n); END; / DECLARE p_B BOOLEAN := TRUE; n NUMBER := 100; BEGIN raise_error(p_B, n); dbms_output.put_line(n); END; / |
|||||||||||||||||||||
AUTHID | ||||||||||||||||||||||
Note: For DEFINER RIGHTS objects execute privileges must be granted directly to the user; not to a role. With CURRENT USER rights can be granted to a role. | ||||||||||||||||||||||
AUTHID Demo |
AUTHID DEFINER (the default) run the procedure with the rights of the procedure's owner. AUTHID CURRENT_USER run the procedure with the rights of the executing schema. |
|||||||||||||||||||||
conn / as sysdba CREATE USER abc IDENTIFIED BY abc DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA 10M ON uwdata; GRANT create session, create table, create procedure to abc; conn abc/abc CREATE TABLE t1 ( mycol VARCHAR2(20)); CREATE TABLE t2 ( yourcol NUMBER(10,2)); CREATE TABLE t3 ( ourcol DATE); conn uwclass/uwclass CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS BEGIN FOR rec IN (SELECT table_name FROM user_tables) LOOP dbms_output.put_line(rec.table_name); END LOOP; END definer_test; / CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS BEGIN FOR rec IN (SELECT table_name FROM user_tables) LOOP dbms_output.put_line(rec.table_name); END LOOP; END cu_test; / set serveroutput on exec definer_test; exec cu_test; GRANT execute on definer_test TO abc; GRANT execute on cu_test TO abc; conn abc/abc set serveroutput on exec uwclass.definer_test; exec uwclass.cu_test; --====================================== conn abc/abc CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS BEGIN FOR rec IN (SELECT srvr_id FROM servers) LOOP dbms_output.put_line(rec.srvr_id); END LOOP; END definer_test; / CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS BEGIN FOR rec IN (SELECT srvr_id FROM servers) LOOP dbms_output.put_line(rec.srvr_id); END LOOP; END cu_test; / set serveroutput on exec definer_test; exec cu_test; GRANT execute on definer_test TO uwclass; GRANT execute on cu_test TO uwclass; conn uwclass/uwclass set serveroutput on exec abc.definer_test; exec abc.cu_test; |
||||||||||||||||||||||
Procedure Demos | ||||||||||||||||||||||
Calculate Business Days |
CREATE TABLE daterange ( beg_date DATE, end_date DATE, biz_days NUMBER(5)); INSERT INTO daterange VALUES (SYSDATE-10, SYSDATE+10, NULL); INSERT INTO daterange VALUES (SYSDATE-17, SYSDATE+10, NULL); INSERT INTO daterange VALUES (SYSDATE-22, SYSDATE+12, NULL); CREATE OR REPLACE PROCEDURE bizdays AUTHID CURRENT_USER IS CURSOR bd_cur IS SELECT beg_date, end_date, 0 FROM daterange; TYPE bDate_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER; bdate bDate_tab; TYPE eDate_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER; edate eDate_tab; TYPE bDay_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; bDays bDay_tab; NumDays PLS_INTEGER; TestDate DATE; BEGIN OPEN bd_cur; LOOP FETCH bd_cur BULK COLLECT INTO bDate, eDate, bDays LIMIT 100; EXIT WHEN bd_cur%NOTFOUND; FOR i IN bdate.FIRST .. bdate.LAST LOOP TestDate := bdate(i); NumDays := 0; FOR j IN 1 .. (edate(i) - bdate(i) + 1) LOOP IF TO_CHAR(TestDate, 'D') BETWEEN '2' AND '6' THEN NumDays := NumDays+1; END IF; TestDate := TestDate + 1; END LOOP; bDays(i) := NumDays; UPDATE daterange SET biz_days = bDays(i) WHERE beg_date = bDate(i) AND end_date = eDate(i); END LOOP; END LOOP; COMMIT; CLOSE bd_cur; END bizdays; / |
'Dev > Oracle' 카테고리의 다른 글
[펌] oralce analyze 관련 구문 (0) | 2009.02.25 |
---|---|
[펌] oracle import 관련 구문 (0) | 2007.11.22 |
[펌] oracle table 관련 구문 (0) | 2007.10.26 |
[펌] oracle tablespace 관련 구문 (0) | 2007.10.11 |
[펌] oracle update 구문 (1) | 2007.10.01 |
- Total
- Today
- Yesterday
- Git
- 정부과천청사
- 출근
- VMware
- GB-P100
- Java
- 일하는 곳
- Eclipse
- 낭패
- 충동구매
- 프로젝트
- 윈도우
- 무료 프로그램
- ubuntu
- 액땜
- 바이크
- 금연
- connect by
- 법무부
- EditPlus
- 김용
- 안드로이드 앱
- iBATIS
- 오라클
- 짜증
- 헌혈
- oracle
- 파워콤해지
- 출근길
- 최대 메모리
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |