분류 전체보기 (658) 썸네일형 리스트형 049 - [Oracle PL/SQL] Package - recompile the package When & Why to recompile the package? # 최초 패키지 create or replace package p_test is c_var1 constant number:=10; c_var2 varchar2(100):='welcome'; procedure print; end; / create or replace package body p_test is c_var3 varchar2(100):='hi there'; procedure print is c_var4 varchar2(100):='hi'; begin dbms_output.put_line('this variable came from package spec. '||c_var1); dbms_output.put_line('this vari.. 048 - [Oracle PL/SQL] Package - visibility of components # c_var1/c_var2 can be referenced any place in package body # c_var3 can be referenced any place in package body # c_var4 can be referenced only in print procedure create or replace package p_test is c_var1 constant number:=10; c_var2 varchar2(100):='welcome'; procedure print; end; / create or replace package body p_test is c_var3 varchar2(100):='hi there'; procedure print is c_var4 varchar2(100.. 047 - [Oracle PL/SQL] Package - guidelines for packages 패키지를 만들때 알아야하는 기본 사항들은 아래와 같습니다. 046 - [Oracle PL/SQL] Package - without body 경우에 따라서 바디가 없는 패키지를 만들때가 있다. 아래와 같은 경우가 그러하다 # we can create package specification without body # this used when we want to define global variable create or replace package global_Measurement is c_mile_to_km constant number:=1.6093; c_kilo_to_mile constant number:=0.6214; end; execute dbms_output.put_line('60 mile:='||60* global_Measurement.c_mile_to_km||' KM' ); --------------------------------.. 045 - [Oracle PL/SQL] Package - Sample create table student ( student_id number, first_name varchar2(100), birthday date, constraint student_pk primary key (student_id) ); create sequence student_seq; --we need to create a package for insert, delete, query a student --1 we create the package spec. create or replace package General_student is procedure insert_student (p_first_name varchar2, p_birthday date); procedure delete_student (.. 044 - [Oracle PL/SQL] Package # create a function to calc square area create or replace function square_area ( p_side number ) return number is begin return p_side*p_side; end; select square_area(4) from dual; -------------------------------------------- SQUARE_AREA(4) -------------- 16 # create a function to calc rectangle area create or replace function rectangle_area ( p_l number,p_w number ) return number is begin return.. 043 - [Oracle PL/SQL] Restrictions when calling functions When a function is called from update/ delete , then then the function can not Query or modify database tables modified by that statement Error: mutating table create or replace function get_sal_tax (p_sal number) return number is begin commit;-- 컴파일 오류는 없지만 함수호출시 오류발생함. if p_sal2000 order by get_sal_tax(salary); ------------------------------------------------------------ ORA-14552: cannot perf.. 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 이전 1 ··· 7 8 9 10 11 12 13 ··· 83 다음
