분류 전체보기 (657) 썸네일형 리스트형 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.. 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_.. 이전 1 ··· 8 9 10 11 12 13 14 ··· 83 다음
