분류 전체보기 (667) 썸네일형 리스트형 042 - [Oracle PL/SQL] Functions - user-defined functions # create a function to calc tax on salary # if salary 2000 order by get_sal_tax(salary); ------------------------------------------------------------------ EMPLOYEE_ID FIRST_NAME SALARY TAX ----------- -------------------- ---------- ---------- 146 Karen 13500 2025 145 John 14000 2100 101 Neena 17000 2550 102 Lex 17000 2550 100 Steven 24100 3615 041 - [Oracle PL/SQL] Functions - no_data_found # NOW LET TRY THIS # no data found not raised in select query, because the function not have exception # PL/SQL 로 실행하면 오류가 발생한다 begin dbms_output.put_line(get_sal(9999)) ; end; ---------------------------------------------------- Error report - ORA-01403: no data found ORA-06512: at "HR.GET_SAL", line 7 ORA-06512: at line 2 01403. 00000 - "no data found" *Cause: No data was found from the object.. 040 - [Oracle PL/SQL] Creating function # create a function to return the salary for an employee # so we need one parameter (in ) number ( employee_id ) # the return value should be also number because it is the salary create or replace function get_sal (p_emp_id number) return number is v_sal number; begin select salary into v_sal from employees where employee_id=p_emp_id; return v_sal; end; ------------------------------------------.. 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.. 이전 1 ··· 9 10 11 12 13 14 15 ··· 84 다음
