Database (237) 썸네일형 리스트형 039 - [Oracle PL/SQL] Functions Examples : • we can crate function to return the salary for an employee • Function to retrieve the full name for the employee • Function to calculate the GPA for the Student • Function to compute the tax for a salary • Host variables not allowed, also substitute variables • It should be at least one return expression in executable section • Return datatype should be without size. • Out / IN OUT .. 038 - [Oracle PL/SQL] Procedure - Boolean/records as parameters # boolean을 이용한 조건문으로 로직처리 create or replace PROCEDURE p(x boolean) -- default is IN is begin if x then DBMS_OUTPUT.PUT_LINE('x is true'); end if; end; ----- declare v boolean; begin v:=true; p(v); end; ---------------- x is true # records를 이용하여 인자전달 create or replace PROCEDURE test_plsql_records ( rec in DEPARTMENTS%rowtype ) is begin insert into DEPARTMENTS values rec; end; ------- declare v DE.. 037 - [Oracle PL/SQL] Procedure - Exception handling in multiple blocks delete products; select * from products; create or replace procedure add_products ( p_prod_id number, p_prod_name varchar2:='Ukowun', p_prod_type varchar2 default 'Ukowun') is begin insert into products values (p_prod_id,p_prod_name,p_prod_type); dbms_output.put_line(p_prod_id||' '||p_prod_name||' inserted ' ); commit; exception when others then dbms_output.put_line ('error in insert '||p_prod_i.. 036 - [Oracle PL/SQL] Procedure - Default option for Parameters # using the default value # 2 ways for default value ( default value or := ) create or replace procedure add_products ( p_prod_id number, p_prod_name varchar2:='Ukowun', -- 035 - [Oracle PL/SQL] Procedure - 인자 전달하는 방법 3가지 # 테스트용 테이블과 프로시져를 생성 --drop table products; create table products ( prod_id number, prod_name varchar2(20), prod_type varchar2(20), constraint products_pk primary key (prod_id) ); ---------------------------------------------------------------- Table PRODUCTS created. ---------------------------------------------------------------- create or replace procedure add_products (p_prod_id number,p_pro.. 034 - [Oracle PL/SQL] Procedure - (IN OUT parameter) --assume the lenght for tel is 12 --example 971505914988 --we need a procedure to format the 971505914988 to 971(50)5914988 create or replace procedure formate_tel (p_tel in out varchar2) is begin p_tel:=substr(p_tel,1,3)||'('||substr(p_tel,4,2)||')'||substr(p_tel,7); end; ---------------- Procedure FORMATE_TEL compiled variable b_telephone varchar2(20); --변수에 값을 입력할때 아래처럼 해야함, 변수명앞에 : 넣어야 오류가 없.. 033 - [Oracle PL/SQL] Procedure - (OUT parameter) /* 1- create a procedure that take emp_id as parameter and return the firat_name and salary note: use bind variable to print the firat_name and salary */ create or replace procedure query_emp ( p_emp_id employees.employee_id%type, p_f_name out employees.first_name%type, p_sal out employees.salary%type ) is begin select first_name,salary into p_f_name,p_sal from employees where employee_id=p_emp_.. 032 - [Oracle PL/SQL] Procedure - (IN parameter)/ Error 처리 # CLI 환경에서 컴파일하는 방법 # 세미콜론을 누락해서 오류가 발생한 상황과 오류를 조회하는 방법 SQL> CREATE OR REPLACE PROCEDURE UPDATE_SAL (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER) IS --here you define variables --n number; BEGIN UPDATE employees set salary=salary+P_AMOUNT where employee_id=P_EMP_ID -- 세미콜른 삭제하고 컴파일해서 오류 확인하기 commit; exception WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); DBMS_OUTPUT.PUT_LINE (SQLERRM); END; 2 3 4.. 이전 1 ··· 8 9 10 11 12 13 14 ··· 30 다음
