-- ..
/* .. */
컴파일(?)하지 않아도 된다.
declare
v_empno number;
v_ename varchar2(14)[ := NULL];
v_job varchar2(13);
begin
select empno, ename, v_job into v_empno, v_ename, v_job from emp where empno = 7900;
update emp set sal = sal * 1.1 where job = v_job;
commit;
end;
/
데이터베이스 사용자는 자신의 스키마에 생성된 PL/SQL 만을 실행할 수 있다.
$ sqlplus scott/tiger
SQL> create table log_table
(userid varchar2(10),
log_date date);
$ vi log_execution.sql
CREATE OR REPLACE PROCEDURE log_execution
IS
i binary_integer := 0;
BEGIN
INSERT INTO log_table(userid, log_date)
VALUES(user, sysdate);
END[ log_execution];
/
SQL> @log_execution.sql
SQL> show errors
SQL> drop procedure fire_emp;
$ vi fire_emp.sql
CREATE OR REPLACE PROCEDURE fire_emp(v_emp_no IN emp.empno%TYPE[ DEFAULT ..])
IS
BEGIN
log_execution; -- 다른 프로시저 내에서 호출할 때는 execute가 필요없다
DELETE FROM emp WHERE empno = v_emp_no;
END[ fire_emp];
/
SQL> @fire_emp.sql
SQL> execute fire_emp([v_emp_no=>]7654)
or
SQL> begin fire_emp([v_emp_no=>]7654); end;
2 /
SQL> select * from EMP;
SQL> select * from LOG_TABLE;
$vi query_emp.sql
create or replace procedure query_emp
(v_emp_no in emp.empno%TYPE,
v_emp_name out emp.ename%TYPE,
v_emp_sal out emp.sal%TYPE,
v_emp_comm out emp.comm%TYPE)
is
begin
select ename, sal, comm into v_emp_name, v_emp_sal, v_emp_comm from emp where empno = v_emp_no;
end[ query_emp];
/
SQL> @query_emp.sql
SQL> variable emp_name varchar2(15)
SQL> variable emp_sal number
SQL> variable emp_comm number
SQL> execute query_emp(7900, :emp_name, :emp_sal, :emp_comm)
SQL> print emp_name
$vi add_one.sql
create or replace procedure add_one(v_phone_no in out VARCHAR2)
is
begin
v_phone_no := SUBSTR(v_phone_no, 1, 1) || 1 || SUBSTR(v_phone_no, 2, length(v_phone_no));
end[ add_one];
/
SQL> @add_one.sql
SQL> variable phone_num varchar2(15)
SQL> begin :phone_num := '0344-860066'; end;
2 /
SQL> execute add_one(:phone_num);
SQL> print phone_num
create or replace function tax(v_value in number) return number
is
begin
return (v_value * 0.07);
end tax;
/
SQL> variable x number
SQL> execute :x := tax(100)
SQL> print x
SQL> select sal, tax(sal) from emp where empno = 7900;
create or replace package emp_comm
(is|as)
g_comm number := 10;
procedure reset_comm(v_comm in number);
--PRAGMA RESTRICT_REFERENCES(reset_comm, WNDS, RNDS);
end emp_comm;
/
create or replace package body emp_comm
(is|as)
function validate_comm(v_comm in number) return boolean
is
v_max_comm number;
begin
select max(comm) into v_max_comm from emp;
if v_comm > v_max_comm then
return (false);
else
return (true);
end if;
end validate_comm;
procedure reset_comm(v_comm in number)
is
v_valid boolean;
begin
v_valid := validate_comm(v_comm);
if v_valid = true then
g_comm := v_comm;
dbms_output.put_line(g_comm);
else
raise_application_error(-20210, 'Invalid Commission');
end if;
end reset_comm;
procedure reset_comm(v_comm in number, va_sal in varchar2)
is
begin
raise_application_error(-20210, 'Invalid Commission');
end reset_comm;
begin
select avg(sal) into g_comm from emp;
dbms_output.put_line(g_comm);
end emp_comm;
/
SQL> execute emp_comm.reset_comm(1500);