记一次写oracle的存储过程

CREATE OR REPLACE PROCEDURE 方法名(patientId varchar2,patientGrender varchar2,patientAddr varchar2,hospitalName varchar2,departmentName varchar2,doctorName varchar2,doctorTitleName varchar2,visitNo varchar2,diagnoseResult varchar2)
is     --或者as也可以
patientName varchar2(256);
phone varchar2(256);
id_no varchar2(256);
yf791_id varchar2(256);
prescription_id varchar2(256);
patient_age varchar2(256);

--定义变量
begin

execute immediate 'select name,phone,id_no into patientName,phone,id_no from users where id ='|| patientId ;  --这1句抵下面6句，给不同字段结果取出赋值，execute immediate 执行sql，后面是sql语句拼接
--patientName := 'select name from users where id = '|| patientId;
--execute immediate patientName into patientName;
--phone := 'select phone from users where id = '|| patientId;
--execute immediate phone into phone;
--id_no := 'select id_no from users where id = '|| patientId;
--execute immediate id_no into id_no;

yf791_id := 'SELECT SEQ_YYT_791YF_PRESCRIPTION.NEXTVAL FROM dual';
execute immediate yf791_id into yf791_id;
--【:=是后面赋值，即sql语句赋值给了yf791_id变量，然后再执行变量赋值(into)给yf791_id】

prescription_id := 'SELECT YF_PRESCRIPTION_ID.NEXTVAL FROM dual';
execute immediate prescription_id into prescription_id;
 
patient_age := 'select trunc(trunc(to_char(sysdate,''yyyyMMdd'')-to_char(to_date(substr('''||id_no||''',7,8),''yyyy-MM-dd''),''yyyyMMdd''))/10000) age from dual';  --oracle的sql语句根据身份证号计算当前用户年龄
execute immediate patient_age into patient_age;

INSERT INTO PRESCRIPTION( ID,PRESCRIPTION_ID,PATIENT_ID,PATIENT_NAME,PATIENT_GENDER,PATIENT_AGE,PATIENT_CARD_ID,PATIENT_PHONE,PATIENT_ADDR,HOSPITAL_NAME,DEPARTMENT_NAME,DOCTOR_NAME,DOCTOR_TITLE_NAME,VISIT_NO,DIAGNOSE_RESULT,CREATE_TIME ) VALUES (to_number(yf791_id), prescription_id, patientId,patientName, patientGrender,patient_age,id_no,phone,patientAddr,hospitalName,departmentName,doctorName,doctorTitleName,visitNo,diagnoseResult,sysdate);

end;